Archive

Archive for the ‘Handy Scripts’ Category

Drop / Recreate FKs

November 7, 2014 Leave a comment

Original at: http://www.codeproject.com/Tips/740561/SQL-Script-to-Drop-and-Re-create-All-Foreign-Keys

;WITH RefColumns AS

    (
SELECT
C.referenced_object_id AS [object_id],
C.parent_object_id,
STUFF((
SELECT ', ' + QUOTENAME(B.name)
FROM sys.foreign_key_columns A
JOIN sys.columns B ON B.[object_id] = A.referenced_object_id
AND B.column_id = A.referenced_column_id
WHERE C.parent_object_id = A.parent_object_id
AND C.referenced_object_id = A.referenced_object_id
FOR XML PATH('')), 1, 2, '') AS ColumnNames
FROM sys.foreign_key_columns C
GROUP BY C.referenced_object_id, C.parent_object_id
)
,
ParentColumns AS
(
SELECT
C.parent_object_id AS [object_id],
C.referenced_object_id,
STUFF((
SELECT ', ' + QUOTENAME(B.name)
FROM sys.foreign_key_columns A
JOIN sys.columns B ON B.[object_id] = A.parent_object_id
AND B.column_id = A.parent_column_id
WHERE C.parent_object_id = A.parent_object_id
AND C.referenced_object_id = A.referenced_object_id
FOR XML PATH('')), 1, 2, '') AS ColumnNames
FROM sys.foreign_key_columns C
GROUP BY C.parent_object_id, C.referenced_object_id
)
SELECT
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' +
QUOTENAME(PT.name) + ' DROP  CONSTRAINT' + ' ' + QUOTENAME(FK.name)                                                        AS [DropFKScript],
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' +
QUOTENAME(PT.name) + ' WITH CHECK ADD  CONSTRAINT '+ QUOTENAME(FK.name) + CHAR(13) + CHAR(10) +
'FOREIGN KEY(' + PC.ColumnNames + ')' + CHAR(13) + CHAR(10) +
'REFERENCES ' + QUOTENAME(SCHEMA_NAME(RT.[schema_id])) + '.' +
QUOTENAME(RT.name) + ' (' + RC.ColumnNames + ')' + CHAR(13) +
CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' +
QUOTENAME(PT.name) + ' CHECK CONSTRAINT ' + QUOTENAME(FK.name) +
CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)                                                                            AS [CreateFKScript]
FROM sys.foreign_keys FK
JOIN sys.tables PT ON PT.[object_id] = FK.parent_object_id
JOIN ParentColumns AS PC ON PC.[object_id] = FK.parent_object_id
AND PC.referenced_object_id = FK.referenced_object_id
JOIN sys.tables RT ON RT.[object_id] = FK.referenced_object_id
JOIN RefColumns AS RC ON RC.[object_id] = FK.referenced_object_id
AND RC.parent_object_id = FK.parent_object_id
WHERE PT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
AND
RT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
--and (RT.name like 'Award%' or RT.name like 'MasterBreeder%' or RT.name = 'AverageRegistrationGroup')
AND RT.name LIKE 'Customer%'
ORDER BY PT.name

Advertisements

Viewing index fragmentation

November 7, 2013 Leave a comment

All tables in a given database

SELECT DB_NAME(database_id) AS 'databasename',
	OBJECT_NAME([OBJECT_ID]) AS 'tablename',
	index_type_desc AS index_type_desc,
	avg_fragmentation_in_percent AS '% fragmentation',
	fragment_count '# fragments',
	page_count,
	avg_fragment_size_in_pages 'Avg frag size in pages'
FROM   sys.dm_db_index_physical_stats (DB_ID('[database_name]'),null,null,null,null );

Single given table in a database

SELECT DB_NAME(database_id) AS 'databasename',
	OBJECT_NAME([OBJECT_ID]) AS 'tablename',
	index_type_desc AS index_type_desc,
	avg_fragmentation_in_percent AS '% fragmentation',
	fragment_count '# fragments',
	page_count,
	avg_fragment_size_in_pages 'Avg frag size in pages'
FROM   sys.dm_db_index_physical_stats (DB_ID('[database_name]'),OBJECT_ID('[table_name]'),null,null,'DETAILED' );

I/O pressure

November 7, 2013 Leave a comment
-- Read
  SELECT --TOP 20 
	qt.text AS 'SP Name', qs.total_physical_reads, qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads',
    qs.execution_count AS 'Execution Count',
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',  
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes,  
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid 
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    --WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_physical_reads DESC

-- Write
   SELECT TOP 20 qt.text AS 'SP Name', qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites',
    qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Logical Writes/Min',  
    qs.execution_count AS 'Execution Count', 
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', 
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, 
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
    qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qt.dbid
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    --WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_logical_writes DESC

 

CPU Pressure

November 7, 2013 Leave a comment
SELECT TOP 50 
      SUM(qs.total_worker_time) AS total_cpu_time, 
      SUM(qs.execution_count) AS total_execution_count,
      COUNT(*) AS  number_of_statements, 
      qs.sql_handle 
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC
SELECT 
      total_cpu_time, 
      total_execution_count,
      number_of_statements,
      s2.text
FROM 
      (SELECT TOP 50 
            SUM(qs.total_worker_time) AS total_cpu_time, 
            SUM(qs.execution_count) AS total_execution_count,
            COUNT(*) AS  number_of_statements, 
            qs.sql_handle
      FROM 
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats 
ORDER BY [Avg CPU Time] DESC

Most “expensive” queries

November 7, 2013 Leave a comment
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

Transaction log file information

November 7, 2013 Leave a comment

General T-Log info

all databases

select name
		, physical_name
		, state_desc
		, (size * 8)/1024 AS [Total Size in MB]
		, (size * 8)/1024 - ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) as [Used Space In MB]
		, ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) AS [Available Space In MB]
		, max_size
		, growth
		, is_percent_growth
from sys.master_files where type_desc = 'LOG';

given database

select name
		, physical_name
		, state_desc
		, (size * 8)/1024 AS [Total Size in MB]
		, (size * 8)/1024 - ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) as [Used Space In MB]
		, ((size * 8)/1024 - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) * 8)/1024) AS [Available Space In MB]
		, max_size
		, growth
		, is_percent_growth
from sys.database_files where type_desc = 'LOG';

detailed transaction log info

select * from ::fn_dblog(null, null);

Check VLFs

November 7, 2013 Leave a comment

given database

DBCC LogInfo;

all databases on the server

declare @v_sql nvarchar(max);
select @v_sql = coalesce(@v_sql + CHAR(13) + char(10), '') + N'
use [' + name + '];' + CHAR(13) +
'DBCC LogInfo;'
from sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB');
print @v_sql
exec sp_executesql @v_sql
Categories: Handy Scripts Tags: