Usefull Queiries

 Following quick script demonstrates last ran queries along with the time it was executed.




SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC



-------------------------------------------------------------------------------------------------------------
 Gives the total number of tables available for a given schema.



SELECT count(*) TABLES, table_schema

FROM information_schema.TABLES

WHERE table_schema= 'dbo' and TABLE_TYPE ='BASE TABLE'

GROUP BY table_schema





Information_schema can used for getting many other information like column details, domain, privileges and many more



 Want to find a function or procedure you can use this query



SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'






 Tables that do not have clustured indexes

SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name

FROM sys.tables AS t

WHERE NOT EXISTS

(

SELECT * FROM sys.indexes AS i

WHERE i.object_id = t.object_id

AND type_desc = 'CLUSTERED'

)

ORDER BY schema_name, table_name;



 Find dependies of specified function



SELECT OBJECT_NAME(object_id) AS referencing_object_name

,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name,*

FROM sys.sql_dependencies

WHERE referenced_major_id = OBJECT_ID('o2_AccountUsers')

ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);