1. Following set of queries can be used to get various values from the database.
- Find all columns in a Database
SELECT SC.name AS ColumnName,
ST.name AS TableName
FROM sys.columns SC
INNER JOIN sys.tables ST
ON ST.object_id = SC.object_id
ORDER BY ST.name,
SC.name
- List all the Functions in a Database
SELECT name AS function_name,
type_desc
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
- List all the Stored Procedures in a Database
SELECT name
FROM sys.procedures
ORDER BY name
- List all the views in a Database
SELECT name
FROM sys.views
ORDER BY name
- List all the tables in a Database
SELECT name
FROM sys.tables
- Find specific column and related table names
SELECT t.name AS table_name,SCHEMA_NAME(schema_id) AS schema_name,c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%column name%'ORDER BY schema_name, table_name
SELECT
c.name 'Column Name',
t.Name 'Data type',
c.max_length 'Max Length',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM
sys.columns c
INNER JOIN
sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
Find specific column and related table names
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%column name%'
ORDER BY schema_name, table_name
2. Queries to check if best practice is followed in SPs
SELECT routine_name,
routine_definition
FROM information_schema.routines
WHERE routine_definition LIKE '%select * from%'
AND routine_type = 'PROCEDURE'
Note: Verify for each combination, i.e., select*from, select *from, select* from.
Query to identify if any SP uses IN or Not IN
SELECT routine_name,
routine_definition
FROM information_schema.routines
WHERE routine_definition LIKE '% in (%'
AND routine_type = 'PROCEDURE'
SELECT routine_name,
routine_definition
FROM information_schema.routines
WHERE routine_definition LIKE '% not in (%'
AND routine_type = 'PROCEDURE'
Note: Verify for each combination of IN and Not IN, i.e., ‘IN(‘, ‘ IN (‘, ‘NotIN(‘, ‘Not IN(‘, ‘Not IN (‘.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION not LIKE '%================%'
AND ROUTINE_TYPE='PROCEDURE'
3. Following queries can be used to get performace details of the database
- CPU utilization in seconds
SELECT (total_worker_time * 1.0) / 1000000 AS CPU_Utilized_in_Seconds, text,*
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
order by CPU_Utilized_in_Seconds desc
- To get longest running query, memory used & other details
SELECT Top 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC
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
WITH cte1
AS (SELECT [dopc].[object_name],
[dopc].[instance_name],
[dopc].[counter_name],
[dopc].[cntr_value],
[dopc].[cntr_type],
Row_number()
OVER (
partition BY [dopc].[object_name], [dopc].[instance_name]
ORDER BY [dopc].[counter_name] ) AS r_n
FROM [sys].[dm_os_performance_counters] AS dopc
WHERE [dopc].[counter_name] LIKE '%Cache Hit Ratio%'
AND ( [dopc].[object_name] LIKE '%Plan Cache%'
OR [dopc].[object_name] LIKE '%Buffer Cache%' )
AND [dopc].[instance_name] LIKE '%_Total%')
SELECT CONVERT(DECIMAL(16, 2), ( [c].[cntr_value] * 1.0 / [c1].[cntr_value] ) *
100.0)
AS [hit_pct]
FROM [cte1] AS c
INNER JOIN [cte1] AS c1
ON c.[object_name] = c1.[object_name]
AND c.[instance_name] = c1.[instance_name]
WHERE [c].[r_n] = 1
AND [c1].[r_n] = 2;
DECLARE @single DECIMAL(18, 2)
DECLARE @reused DECIMAL(18, 2)
DECLARE @total DECIMAL(18, 2)
SELECT @single = Sum(CASE ( usecounts )
WHEN 1 THEN 1
ELSE 0
END) * 1.0,
@reused = Sum(CASE ( usecounts )
WHEN 1 THEN 0
ELSE 1
END) * 1.0,
@total = Count(usecounts) * 1.0
FROM sys.dm_exec_cached_plans;
SELECT ( @single / @total ) * 100.0;
- Identify if a column is reaching limit of the datatype
WITH cte_1
AS (SELECT Object_name(a.object_id) AS table_name,
a.name AS columnname,
CONVERT(BIGINT, Isnull(a.last_value, 0)) AS last_value,
CASE
WHEN b.name = 'tinyint' THEN 255
WHEN b.name = 'smallint' THEN 32767
WHEN b.name = 'int' THEN 2147483647
WHEN b.name = 'bigint' THEN 9223372036854775807
END AS dt_value
FROM sys.identity_columns a
INNER JOIN sys.types AS b
ON a.system_type_id = b.system_type_id),
cte_2
AS (SELECT *,
CONVERT(NUMERIC(18, 2), ( ( CONVERT(FLOAT, last_value) /
CONVERT(FLOAT, dt_value) ) * 100 ))
AS 'Percent'
FROM cte_1)
SELECT *
FROM cte_2
ORDER BY 'Percent' DESC;
SELECT db_name() as dbname,
o.name as tablename,
i.name as indexname,
i.index_id,
user_seeks + user_scans + user_lookups as total_reads,
user_updates as total_writes,
(SELECT SUM(p.rows)
FROM sys.partitions p
WHERE p.index_id = s.index_id
AND s.object_id = p.object_id) as number_of_rows,
s.last_user_lookup,
s.last_user_scan,
s.last_user_seek
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats s
ON i.index_id = s.index_id
AND s.object_id = i.object_id
WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
AND ISNULL(s.database_id, DB_ID()) = DB_ID()
AND (
isnull(s.last_user_seek, '19000101') < datediff(month, -1, getdate()) AND
isnull(s.last_user_scan, '19000101') < datediff(month, -1, getdate()) AND
isnull(s.last_user_lookup, '19000101') < datediff(month, -1, getdate())
)
ORDER BY total_reads DESC;
- Tables without clustered indexes defined
(
|
SELECT db_name() as dbname,
|
FROM sys.partitions p
|
WHERE p.index_id = i.index_id
|
AND i.object_id = p.object_id) as number_of_rows
|
FROM sys.indexes i
|
INNER JOIN sys.objects o
|
ON i.object_id = o.object_id
|
WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
|
AND OBJECTPROPERTY(o.object_id, 'TableHasClustIndex') = 0
|
FROM CTE_1
|
WHERE number_of_rows > 1000;
|
WITH indexcols
AS (SELECT object_id AS id,
index_id AS indid,
name,
(SELECT CASE keyno
WHEN 0 THEN NULL
ELSE colid
END AS [data()]
FROM sys.sysindexkeys AS k
WHERE k.id = i.object_id
AND k.indid = i.index_id
ORDER BY keyno,
colid
FOR
XML PATH('')
) AS cols,
(SELECT CASE keyno
WHEN 0 THEN colid
ELSE NULL
END AS [data()]
FROM sys.sysindexkeys AS k
WHERE k.id = i.object_id
AND k.indid = i.index_id
ORDER BY colid
FOR
XML PATH('')
) AS inc
FROM sys.indexes AS i
)
SELECT DB_NAME() AS 'DBName',
OBJECT_SCHEMA_NAME(c1.id) + '.'
+ OBJECT_NAME(c1.id) AS 'TableName',
c1.name + CASE c1.indid
WHEN 1 THEN ' (clustered index)'
ELSE ' (nonclustered index)'
END AS 'IndexName',
c2.name + CASE c2.indid
WHEN 1 THEN ' (clustered index)'
ELSE ' (nonclustered index)'
END AS 'ExactDuplicatedIndexName'
FROM indexcols AS c1
INNER JOIN indexcols AS c2
ON
c1.id = c2.id
AND c1.indid < c2.indid
AND c1.cols = c2.cols
AND c1.inc = c2.inc;
DECLARE @Result INT;
DECLARE @High INT; -- For settings you feel need to be righ
DECLARE @Med INT; -- For settings you want to know about but arent critical
DECLARE @Low INT; -- For settings that you want flagged but are low importance
SELECT @High = 70,
@Med = 40,
@Low = 10;
SELECT @Result = SUM(CASE WHEN [d].[compatibility_level] != [d2].[compatibility_level]
THEN @Med
ELSE 0
END
+ CASE WHEN [d].[collation_name] != [d2].[collation_name]
THEN @Med
ELSE 0
END
+ CASE WHEN [d].[user_access] != 0
THEN @Low
ELSE 0
END
+ CASE WHEN [d].[is_auto_close_on] = 1
THEN @High
ELSE 0
END
+ CASE WHEN [d].[is_auto_shrink_on] = 1
THEN @High
ELSE 0
END
+ CASE WHEN [d].[state] != 0
THEN @Low
ELSE 0
END
+ CASE WHEN [d].[is_auto_create_stats_on] != 1
THEN @Med
ELSE 0
END
+ CASE WHEN [d].[is_auto_update_stats_on] != 1
THEN @Med
ELSE 0
END
+ CASE WHEN [d].[is_ansi_nulls_on] = 1
THEN @High
ELSE 0
END
+ CASE WHEN [d].[is_ansi_padding_on] = 1
THEN @High
ELSE 0
END
+ CASE WHEN [d].[is_ansi_warnings_on] = 1
THEN @High
ELSE 0
END
+ CASE WHEN [d].[is_arithabort_on] = 1
THEN @High
ELSE 0
END)
FROM [sys].[databases] AS d
CROSS JOIN [sys].[databases] AS d2
WHERE [d2].[name] = 'master'
AND ([d].[database_id] = DB_ID()
AND [d].[state_desc] = 'Online');
SELECT @Result;
Happy Coding!!
