This post is about some queries and scripts that I use in my daily work on SQL Server. Some of them are self-explanatory, i.e. the name of the query/script already describes its purpose. However, if you need to get some more details around, then you may need to add some extra columns to the output results. I often put the output from the queries into temp tables so that I’m able to do an extra querying with other meta-data sets. Some of the queries use a threshold parameter which you can change for your needs. Some others have the opposite version like instead of EXISTS you replace with NOT EXISTS and get another output.
For most of the queries/scripts, you’ll need to have sysadmin permissions over the databases.
Query 1: List databases with size info
SELECT
DB.name,
SUM(CASE WHEN [type] = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
SUM(CASE WHEN [type] = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB
FROM
sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.source_database_id is null -- Exclude snapshots
GROUP BY DB.name
ORDER BY DataFileSizeMB DESCQuery 2: List objects with space info
SELECT
SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(p.object_id) AS [Name],
CONVERT(decimal(18,2),SUM(reserved_page_count) * 8/1024.0) AS Total_space_used_MB,
CONVERT(decimal(18,2),SUM(CASE WHEN index_id < 2 THEN reserved_page_count ELSE 0 END ) * 8/1024.0) AS Table_space_used_MB,
CONVERT(decimal(18,2),SUM(CASE WHEN index_id > 1 THEN reserved_page_count ELSE 0 END ) * 8/1024.0) AS Nonclustered_index_spaced_used_MB,
MAX(row_count) AS Row_count
FROM
sys.dm_db_partition_stats AS p
INNER JOIN sys.all_objects AS o ON p.object_id = o.object_id
WHERE
o.is_ms_shipped = 0
GROUP BY
SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(p.object_id)
ORDER BY
Total_space_used_MB descQuery 3: Find the average size of rows in tables
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
CAST(OBJECT_NAME(ps.OBJECT_ID)+'.'+ISNULL(i.[Name],'heap') AS VARCHAR(60)) AS Table_index_name,
SUM(ps.record_count) AS Sum_record_count,
CAST(((SUM(ps.page_count) * 8192) / 1000000.00) AS NUMERIC(9,2)) AS Size_mb,
AVG(ps.max_record_size_in_bytes) AS Avg_record_size_in_bytes,
MAX(ps.max_record_size_in_bytes) AS Max_record_size_in_bytes,
CAST(AVG(avg_fragmentation_in_percent) AS NUMERIC(6,1)) AS Avg_fragmentation_in_percent,
CAST(AVG(ps.avg_page_space_used_in_percent) AS NUMERIC(6,1)) AS Avg_page_space_used_in_percent
FROM [sys].dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps --Must use DETAILED
LEFT JOIN [sys].indexes AS i ON i.OBJECT_ID = ps.OBJECT_ID AND i.index_id = ps.index_id
--WHERE OBJECT_NAME(ps.OBJECT_ID) IN ('Employee') --Use filtering here if you want results for specific tables only, runs faster on big databases
GROUP BYOBJECT_NAME(ps.OBJECT_ID), i.[Name]
ORDER BYOBJECT_NAME(ps.OBJECT_ID), i.[Name];Query 4: Get fragmentation info for the tables
SELECT ss.[Name] [Schema], OBJECT_NAME(ddips.OBJECT_ID) [Table_name], ISNULL(si.[Name],'') [Index_name], si.Index_id, si.[Type_desc], ISNULL(ddips.avg_fragmentation_in_percent,0) [Ext_fragmentation], ddips.page_count [Pages], si.Fill_factor, ISNULL(ddips.avg_page_space_used_in_percent,0) [Page_fullness_pct] FROM [sys].dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ddips /*DETAILED offers more, but burns the CPU more*/ JOIN [sys].indexes si ON ddips.index_id = si.index_id AND ddips.OBJECT_ID = si.OBJECT_ID JOIN [sys].tables st ON ddips.OBJECT_ID = st.OBJECT_ID JOIN [sys].schemas ss ON st.SCHEMA_ID = ss.SCHEMA_ID WHERE ddips.index_level = 0 AND si.index_id > 0 AND st.[Type] = N'U' /* leaf level, non-heaps, user defined */GROUP BYss.[Name], ddips.OBJECT_ID, si.[Name], si.index_id, si.type_desc, avg_fragmentation_in_percent, ddips.page_count, avg_page_space_used_in_percent,si.fill_factor ORDER BY ddips.page_count DESC;
Query 5: Recommendation for potentially missing indexes. Note: The indexes analysis requires more time, so you may run this query scheduled, saving the output results for a further deeper analysis.
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [IX_' + CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.STATEMENT, 1), 32) + ']'
+ ' ON ' + mid.STATEMENT
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM [sys].dm_db_missing_index_groups mig
INNER JOIN [sys].dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN [sys].dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;Query 6: Find TOP 50 unused indexes in a database. Note: Make sure the SQL Server hasn’t been restarted for a longer period.
SELECT TOP 50
o.[Name] AS [ObjectName]
, i.[Name] AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.[Name])
+ ' ON ' + QUOTENAME(s.[Name]) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'Drop statement'
FROM [sys].dm_db_index_usage_stats dm_ius
INNER JOIN [sys].indexes i ON i.index_id = dm_ius.index_id
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN [sys].objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN (SELECT SUM(p.ROWS) TableRows, p.index_id, p.OBJECT_ID
FROM [sys].partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC;
GOQuery 7: Tables with INSTEAD OF triggers
SELECT s.[Name] + N'.' + t.[Name] [Table] FROM [sys].tables t INNER JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id] WHERE EXISTS ( SELECT * FROM [sys].triggers tr WHERE tr.parent_id = t.[object_id] AND tr.is_instead_of_trigger = 1 );
You can use NOT EXISTS in the WHERE clause to find the tables not having INSTEAD OF triggers.
Query 8: Tables that don’t have Primary Key
SELECT s.[name] + N'.' + t.[name] [Table]
FROM sys.tables t
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE NOT EXISTS
( SELECT * FROM sys.key_constraints kc
WHERE kc.[type] = N'PK' AND kc.parent_object_id = t.[object_id]
);You can easily find the tables with primary keys if you just use EXISTS in the WHERE clause.
Query 9: Find objects that use compression
SELECT
SCHEMA_NAME([sys].objects.SCHEMA_ID) AS [Schema Name]
,OBJECT_NAME([sys].objects.OBJECT_ID) AS [Object Name]
,'ALTER INDEX ALL ON '+SCHEMA_NAME([sys].objects.SCHEMA_ID)+'.'
+OBJECT_NAME([sys].objects.OBJECT_ID)+' REBUILD WITH (DATA_COMPRESSION = None);' [Decompress CMD]
,(SELECT OBJECTPROPERTY(OBJECT_ID(OBJECT_NAME([sys].objects.OBJECT_ID)),
'TableHasVarDecimalStorageFormat') ) AS [Table Has VarDecimal Storage Format]
,[Rows]
,[data_compression_desc] [Compression Type]
,[Index_id] AS [Index ID on Table]
FROM [sys].partitions
INNER JOIN [sys].objects ON [sys].partitions.OBJECT_ID = [sys].objects.OBJECT_ID
WHERE [data_compression] > 0 AND SCHEMA_NAME([sys].objects.SCHEMA_ID) <> 'SYS'
ORDER BY [Schema Name], [Object Name];Script 10: Recompile all programmable objects in a database
DECLARE sps CURSOR FOR
SELECT ROUTINE_NAME
FROM [INFORMATION_SCHEMA].routines
WHERE ROUTINE_TYPE = 'PROCEDURE';
OPEN sps;
DECLARE @RoutineName VARCHAR(128);
DECLARE @SQLString NVARCHAR(2048);
FETCH NEXT FROM sps
INTO @RoutineName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'EXECUTE sp_recompile '+@RoutineName;
PRINT @SQLString;
EXECUTE sp_ExecuteSQL @SQLString;
FETCH NEXT FROM sps
INTO @RoutineName;
END;
CLOSE sps;
DEALLOCATE sps;Script 11: Refresh all views in a database
DECLARE @ActualView VARCHAR(255);
DECLARE viewlist CURSOR FAST_FORWARD
FOR
SELECT
DISTINCT s.[Name] + '.' + o.[Name] AS ViewName
FROM [sys].objects o JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHEREo.[type] = 'V'
AND OBJECTPROPERTY(o.[object_id], 'IsSchemaBound') <> 1
AND OBJECTPROPERTY(o.[object_id], 'IsMsShipped') <> 1;
OPEN viewlist;
FETCH NEXT FROM viewlist
INTO @ActualView;
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @ActualView
BEGIN TRY
EXECUTE sp_refreshview @ActualView;
END TRY
BEGIN CATCH
PRINT 'View '+@ActualView+' cannot be refreshed.';
END CATCH;
FETCH NEXT FROM viewlist
INTO @ActualView;
END;
CLOSE viewlist;
DEALLOCATE viewlist;Query 12: Find all constraints that need to be entrusted. The last column of the result set is the SQL command to execute to entrust the constraint.
SELECT
SCHEMA_NAME(s.[schema_id]) [Schema],
OBJECT_NAME(fk.parent_object_id) [Table],
fk.[name] [Constraint],
CASE is_not_trusted WHEN 1 THEN 'No' ELSE 'Yes' END [Trusted],
fk.[Type_desc],
('ALTER TABLE '+QUOTENAME(SCHEMA_NAME(s.[schema_id]))+'.'+
QUOTENAME(OBJECT_NAME(fk.parent_object_id))+
' WITH CHECK CHECK CONSTRAINT '+fk.name) [SQL command to Entrust the Constraint]
FROM sys.foreign_keys fk
INNER JOIN sys.objects o ON fk.parent_object_id = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE fk.is_not_trusted = 1 AND fk.is_not_for_replication = 0
UNION ALL
SELECT
SCHEMA_NAME(s.[schema_id]) [Schema],
OBJECT_NAME(cc.parent_object_id) [Table],
cc.[name] [Constraint],
CASE is_not_trusted WHEN 1 THEN 'No' ELSE 'Yes' END [Trusted],
cc.[type_desc],
('ALTER TABLE '+QUOTENAME(SCHEMA_NAME(s.[schema_id]))+'.'+
QUOTENAME(OBJECT_NAME(cc.parent_object_id))+
' WITH CHECK CHECK CONSTRAINT '+cc.name) [SQL command to Entrust the Constraint]
FROM sys.check_constraints cc
INNER JOIN sys.objects o ON cc.parent_object_id = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE cc.is_not_trusted = 1 AND cc.is_not_for_replication = 0 AND cc.is_disabled = 0;The version of the query with the cursor (script) so that it entrusts all constraints automatically:
DECLARE @entrust_constraint NVARCHAR(1000);
DECLARE constr_cursor CURSOR FOR
SELECT
('ALTER TABLE '+QUOTENAME(SCHEMA_NAME(s.[schema_id]))+'.'+
QUOTENAME(OBJECT_NAME(fk.parent_object_id))+
' WITH CHECK CHECK CONSTRAINT '+fk.[Name]) AS [EntrustTheConstraint]
FROM [sys].foreign_keys fk
INNER JOIN [sys].objects o ON fk.parent_object_id = o.OBJECT_ID
INNER JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE fk.is_not_trusted = 1 AND fk.is_not_for_replication = 0
UNION ALL
SELECT
('ALTER TABLE '+QUOTENAME(SCHEMA_NAME(s.[schema_id]))+'.'+
QUOTENAME(OBJECT_NAME(cc.parent_object_id))+
' WITH CHECK CHECK CONSTRAINT '+cc.[Name]) AS [EntrustTheConstraint]
FROM [sys].check_constraints cc
INNER JOIN [sys].objects o ON cc.parent_object_id = o.OBJECT_ID
INNER JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE cc.is_not_trusted = 1 AND cc.is_not_for_replication = 0 AND cc.is_disabled = 0;
OPEN constr_cursor;
FETCH NEXT FROM constr_cursor INTO @entrust_constraint;
WHILE (@@FETCH_STATUS=0)
BEGIN
BEGIN TRY
EXECUTE sp_executesql @entrust_constraint;
PRINT 'Successed: '+@entrust_constraint;
END TRY
BEGIN CATCH
PRINT 'Failed: '+@entrust_constraint;
END CATCH;
FETCH NEXT FROM constr_cursor INTO @entrust_constraint;
END;
CLOSE constr_cursor;
DEALLOCATE constr_cursor;Script 13: Kill all user processes for a database. Note: Be careful with this stored procedure! I usually use in the Test/Dev environments.
IF EXISTS (SELECT * FROM [sys].objects
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[sp_kill_sql_db_sys_processes]')
AND [Type] IN (N'P', N'PC')
)
DROP PROCEDURE [dbo].[sp_kill_sql_db_sys_processes];
GO
CREATE PROCEDURE [dbo].[sp_kill_sql_db_sys_processes]
@dbName NVARCHAR(100)
AS
BEGIN
DECLARE @spid INT;
DECLARE @sqlString NVARCHAR(100);
DECLARE conn_cursor CURSOR FOR
SELECT [SPID] FROM [master].[dbo].sysprocesses
WHERE [DbId] = DB_ID(@dbName) AND [SPID] <> @@spid;
OPEN conn_cursor;
FETCH NEXT FROM conn_cursor INTO @spid;
WHILE @@fetch_status=0
BEGIN
SET @sqlString = 'KILL '+CAST(@spid AS NVARCHAR(10));
PRINT @sqlString;
EXECUTE sp_executeSql @sqlString;
FETCH NEXT FROM conn_cursor INTO @spid;
END;
CLOSE conn_cursor;
DEALLOCATE conn_cursor;
END;
GO
Exec sp_kill_sql_db_sys_processes @dbName='your_database_name';Query 14: List all assemblies in a database
SELECT so.[Name], so.[type], SCHEMA_NAME(so.SCHEMA_ID) AS [Schema],
asmbly.[Name], asmbly.permission_set_desc, am.assembly_class,
am.assembly_method
FROM [sys].assembly_modules am
INNER JOIN [sys].assemblies asmbly
ON asmbly.assembly_id = am.assembly_id AND asmbly.[Name] NOT LIKE 'Microsoft%'
INNER JOIN [sys].objects so
ON so.OBJECT_ID = am.OBJECT_ID
UNION
SELECT at.[Name], 'TYPE' AS [type], SCHEMA_NAME(AT.SCHEMA_ID) AS [Schema],
asmbly.[Name], asmbly.permission_set_desc, AT.assembly_class,
NULL AS [assembly_method]
FROM [sys].assembly_types at
INNER JOIN [sys].assemblies asmbly
ON asmbly.assembly_id = at.assembly_id
AND asmbly.[Name] NOT LIKE 'Microsoft%'
ORDER BY 4, 2, 1;Query 15: Check if your dynamic T-SQL statement is valid. Note: I create this stored procedure and use in my scripts and codes.
CREATE PROCEDURE IsValidSQL (@sql VARCHAR(MAX)) AS
BEGIN
BEGIN TRY
SET @sql = 'SET PARSEONLY ON;'+@sql;
EXECUTE(@sql);
END TRY
BEGIN CATCH
RETURN(0); --Fail
END CATCH;
RETURN(1); --Success
END; -- IsValidSQL
--Test:
--Fail
DECLARE @retval INT;
EXECUTE @retval = IsValidSQL 'SELECT IIF(val, 0, 1) FROM T'; --T is not existing
SELECT @retval;
GO
--Success
CREATE TABLE #T(id INT IDENTITY(1,1),val VARCHAR(100));
DECLARE @retval INT;
EXECUTE @retval = IsValidSQL 'SELECT val FROM from #T'; --#T is existing
SELECT @retval;Query 16: All user-created statistics
SELECT st.[Name] [TableName], ss.[Name] StatisticName, sc.[Name] AS [ColumnName], t.[Name] AS DataType, CASE WHEN sc.max_length = -1 THEN 'varchar(max), nvarchar(max), varbinary(max) or xml' ELSE CAST(sc.max_length AS VARCHAR(10)) END AS ColumnLength FROM [sys].stats ss JOIN [sys].tables st ON ss.OBJECT_ID=st.OBJECT_ID JOIN [sys].stats_columns ssc ON ss.stats_id=ssc.stats_id AND st.OBJECT_ID=ssc.OBJECT_ID JOIN [sys].columns sc ON ssc.column_id=sc.column_id AND st.OBJECT_ID=sc.OBJECT_ID JOIN [sys].types t ON sc.system_type_id=t.system_type_id WHERE ss.user_created = 1 ORDER BY t.[Name], st.[Name];
Query 17: Tables with more than 30 columns (wide tables)
DECLARE @threshold INT;
SET @threshold = 30;
;WITH cte AS
(
SELECT [object_id], COUNT(*) [Columns]
FROM sys.columns
GROUP BY [object_id]
HAVING COUNT(*) > @threshold
)
SELECT
s.[name] + N'.' + t.[name] [Table],
c.[Columns]
FROM cte c
INNER JOIN sys.tables t ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
ORDER BY c.[column count] DESC;Query 18: Tables with more than 5 indexes
DECLARE @threshold INT; SET @threshold = 5; SELECT [Table] = s.[Name] + N'.' + t.[Name] FROM [sys].tables t INNER JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id] WHERE EXISTS ( SELECT 1 FROM [sys].indexes i WHERE i.[object_id] = t.[object_id] GROUP BY i.[object_id] HAVING COUNT(*) > @threshold );
Query 19: Tables without a Clustered Index (Heap)
SELECT [Table] = s.[Name] + N'.' + t.[Name] FROM [sys].tables t INNER JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id] WHERE NOT EXISTS ( SELECT 1 FROM [sys].indexes i WHERE i.[object_id] = t.[object_id] AND i.index_id = 1 );
You can easily find the tables with Clustered indexes by just using EXISTS in the WHERE clause.
Query 20: Tables with their rows (fastest way to get tables rows)
SELECT s.[Name] + N'.' + t.[Name] [Table], p.[Rows] FROM [sys].tables t JOIN [sys].schemas s ON s.SCHEMA_ID = t.SCHEMA_ID JOIN [sys].partitions p ON p.OBJECT_ID = t.OBJECT_ID AND p.index_id IN (0,1); --heap or clustered index
Query 21: Tables with XML columns
SELECT [Table] = s.name + N'.' + t.name FROM sys.tables t INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id] WHERE EXISTS ( SELECT 1 FROM sys.columns c WHERE c.[object_id] = t.[object_id] AND c.system_type_id = 241 -- 241 = xml );
Query 22: Tables with at least one LOB (max) column
SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM [sys].columns c
WHERE c.[object_id] = t.[object_id]
AND c.max_length = -1
AND c.system_type_id IN
(
165, -- varbinary
167, -- varchar
231 -- nvarchar
)
);Query 23: Tables with at least one TEXT, NTEXT, IMAGE column
SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM [sys].columns c
WHERE c.[object_id] = t.[object_id]
AND c.system_type_id IN
(
34, -- image
35, -- text
99 -- ntext
)
);Query 24: Tables with Identity columns
SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT * FROM [sys].identity_columns i
WHERE i.[object_id] = t.[object_id]
);You can easily find the tables without identity columns by just using NOT EXISTS in the WHERE clause.
Query 25: Tables with at least two triggers
DECLARE @min_count INT;
SET @min_count = 2;
SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM [sys].triggers tr
WHERE tr.parent_id = t.[object_id]
GROUP BY tr.parent_id
HAVING COUNT(*) >= @min_count
);Query 26: Tables dependency order
;WITH a AS
(
SELECT
0 AS lvl, t.OBJECT_ID AS tblID
FROM [sys].tables t
WHERE t.is_ms_shipped = 0
AND t.OBJECT_ID NOT IN (SELECT f.referenced_object_id
FROM [sys].foreign_keys f)
UNION ALL
SELECT
a.lvl + 1 AS lvl, f.referenced_object_id AS tblId
FROM a
INNER JOIN [sys].foreign_keys f ON a.tblId = f.parent_object_id
AND a.tblID != f.referenced_object_id
)
SELECT
OBJECT_SCHEMA_NAME(tblID) [schema_name], OBJECT_NAME(tblId) [table_name], a.lvl
FROM a
GROUP BY tblId, a.lvl
ORDER BY MAX(lvl), 1;Query 27: View server roles and permissions per Login
SELECT
sp.[Name] AS ServerPrincipal, sp.[type_desc] AS LoginType,
CASE sp.is_disabled
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END AS UserDisabled,
sp.create_date AS DateCreated, sp.modify_date AS DateModified, sp.default_database_name AS DefaultDB, sp.default_language_name AS DefaultLang,
ISNULL(STUFF((
SELECT ',' + CASE ssp22.[Name] WHEN 'sysadmin' THEN ssp22.[Name] + ' "Full privilages"' ELSE ssp22.[Name] END
FROM [sys].server_principals ssp2
INNER JOIN [sys].server_role_members ssrm2 ON ssp2.principal_id = ssrm2.member_principal_id
INNER JOIN [sys].server_principals ssp22 ON ssrm2.role_principal_id = ssp22.principal_id
WHERE ssp2.principal_id = sp.principal_id
ORDER BY ssp2.[Name]
FOR XML PATH (N''), TYPE
).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''), 'No Roles Held') AS ListofServerRoles,
ISNULL(STUFF((
SELECT ';' + ' Permission [' + sspm3.[permission_name] + '] is [' + CASE WHEN sspm3.[state_desc] = 'GRANT' THEN 'Granted]' WHEN sspm3.[state_desc] = 'DENY' THEN 'Denied]' END AS PermGrants
FROM [sys].server_principals ssp3
INNER JOIN [sys].server_permissions sspm3 ON ssp3.principal_id = sspm3.[grantee_principal_id]
WHERE sspm3.[class] = 100 AND sspm3.[grantee_principal_id] = sp.principal_id
FOR XML PATH (N''), TYPE
).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''), 'No Server Permissions') + ' in Server::' + @@ServerName + '' AS PermGrants
FROM [sys].server_principals sp
WHERE sp.[Type] IN ('S', 'G', 'U') AND sp.[Name] NOT LIKE '##%##'
ORDER BY ServerPrincipal;Script 28: Re-align the identity of the tables. If the alignment of Identity matters to your business logic then you go with this script. However, keep in mind the cases when you may have the Lost Identity especially when there is often switching in the clustered environments.
DECLARE @CurrTable SYSNAME, @CurrCol SYSNAME, @LastValue BIGINT
DECLARE @CMD NVARCHAR(MAX), @Result NVARCHAR(MAX)
DECLARE Cur CURSOR
LOCAL FAST_FORWARD
FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(OBJECT_NAME(t.object_id)), c.name, CONVERT(int, c.last_value)
FROM sys.identity_columns AS c
INNER JOIN sys.tables AS t
ON c.object_id = t.object_id
WHERE c.last_value > c.seed_value
OPEN Cur
FETCH NEXT FROM Cur INTO @CurrTable, @CurrCol, @LastValue
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD = N'
SELECT @pResult = N''DBCC CHECKIDENT(''''' + @CurrTable + N''''', RESEED, '' + CONVERT(nvarchar(max), MAX(' + QUOTENAME(@CurrCol) + N')) + N'') -- ' + CONVERT(nvarchar(max), @LastValue) + N'''
FROM ' + @CurrTable + N'
HAVING MAX(' + QUOTENAME(@CurrCol) + N') <> @LastValue'
EXEC sp_executesql @CMD, N'@pResult NVARCHAR(MAX) OUTPUT, @LastValue BIGINT', @Result OUTPUT, @LastValue;
IF @Result IS NOT NULL
PRINT @Result;
FETCH NEXT FROM Cur INTO @CurrTable, @CurrCol, @LastValue
END
CLOSE Cur
DEALLOCATE CurScript 29: Script to create a database snapshot. Note: Be careful with the databases snapshots. They could slow-down the database activities.
You can find it in this link
I usually use the script to create database snapshots temporarily on the Test/Dev servers.
Script 30:
I always use Exec sp_WhoIsActive in the daily work. You can get the amazing stored procedure here.
Script 31:
I use this bunch of scripts by Bernt Ozar as well.
Thanks for reading. You can post some other useful queries and scripts in the comment 