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 DESC

Query 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 desc

Query 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;
GO

Query 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 Cur

Script 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