Consulta para exibir relações de chave estrangeira e nome da restrição para cada tabela no banco de dados

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME


****OPÇÕES****


ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')

Detalhes importantes de todas as bases

- Mostra alguns outros detalhes importantes, bem como: número de índices não clusterizados, existência de PK e CIX, a existência de colunas de texto ou imagem, a existência de índice de texto completo e triggers.

SET NOCOUNT ON
create table #tmp1 (TableName sysname, rows bigint, reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50))
create table #tmp3 (TableName sysname, rows bigint, reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50), DBName sysname )
create table #tmp2 (TableName sysname, HasAfterTrigger BIT, HasInsteadOfTrigger BIT, HasTextImage BIT, HasFulltextIndex BIT, HasPrimaryKey BIT, HasClustIndex BIT, DBName sysname, IndexCount INT)
DECLARE @DBName sysname
DECLARE @sql varchar(8000)
CREATE TABLE #dbs (DBName sysname PRIMARY KEY)
INSERT INTO #dbs (DBName)
SELECT name FROM master..sysdatabases WHERE name not in ('tempdb') and
status & 512 <> 512
DECLARE z CURSOR FOR
SELECT DBName FROM #dbs
open z
FETCH NEXT FROM z INTO @DBName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @sql = '
insert into #tmp1 (TableName, rows, reserved, data, index_size, unused) exec sp_spaceused ''?'';
insert into #tmp3 (TableName, rows, reserved, data, index_size, unused, DBName)
select ''?'', rows, reserved, data, index_size, unused, ''' + @DBName + '''
from #tmp1
truncate table #tmp1
insert into #tmp2
(TableName , HasAfterTrigger , HasInsteadOfTrigger , HasTextImage , HasFulltextIndex , HasPrimaryKey , HasClustIndex, DBName,IndexCount)
select ''?'' AS TableName , objectproperty(object_id(''?''),''HasAfterTrigger'')
,objectproperty(object_id(''?''),''HasInsteadOfTrigger'')
,objectproperty(object_id(''?''),''TableHasTextImage'')
,objectproperty(object_id(''?''),''TableHasActiveFulltextIndex'')
,objectproperty(object_id(''?''),''TableHasPrimaryKey'')
,objectproperty(object_id(''?''),''TableHasClustIndex'')
, ''' + @DBName + '''
,(SELECT count(indid) IndexCount FROM dbo.sysindexes
 WHERE dbo.sysindexes.indid between 2 and 254 AND INDEXPROPERTY(id,name, ''IsStatistics'')=0
AND id = object_id(''?''))
'
SELECT @sql = 'USE ' + QUOTENAME(@DBName) + ';
exec sp_msforeachtable ''' + REPLACE(@sql,'''','''''') + ''',''?'''
exec(@sql)
FETCH NEXT FROM z INTO @DBName
END
CLOSE z
DEALLOCATE z
SET NOCOUNT OFF
select @@ServerName AS ServerName, t0.DBName, t0.TableName, Rows, convert(int,left(reserved, len(reserved)-3)) AS Reserved,
convert(int,left(data, len(data)-3)) AS Data,
convert(int,left(Index_Size, len(Index_Size)-3)) AS Index_Size,
convert(int,left(Unused, len(Unused)-3)) AS Unused,
ISNULL(t1.IndexCount,0) AS NCIndexCount,
HasPrimaryKey, HasClustIndex,
HasTextImage, HasFulltextIndex,
HasAfterTrigger, HasInsteadofTrigger, GETDATE() AS ExecDate
from #tmp3 t0 inner join #tmp2 t1 on t0.TableName = t1.TableName AND t0.DBName = t1.DBName
ORDER BY convert(int,left(data, len(data)-3))+convert(int,left(Index_Size, len(Index_Size)-3)) DESC
drop table #tmp1
drop table #tmp2
drop table #tmp3
drop table #dbs

Mostra Caminhos de BKP configurados no MSSQL

--Mostra Caminhos de BKP configurados no MSSQL

SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily

As consultas que nos solicitou memória ou à espera de memória a ser concedido

-- As consultas que nos solicitou memória ou à espera de memória a ser concedido
SELECT  DB_NAME(st.dbid) AS [DatabaseName] ,
        mg.requested_memory_kb ,
        mg.ideal_memory_kb ,
        mg.request_time ,
        mg.grant_time ,
        mg.query_cost ,
        mg.dop ,
        st.[text]
FROM    sys.dm_exec_query_memory_grants AS mg
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE   mg.request_time < COALESCE(grant_time, '99991231')
ORDER BY mg.requested_memory_kb DESC ;

-- Versão SQL Server 2005/2008/R2
-- Top clerks ordenados por memória usada
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(single_pages_kb) AS [SPA Memory (KB)],
SUM(single_pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC;

-- Versão SQL Server 2005/2008/R2
-- Top clerks ordenados por memória usada
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(pages_kb) AS [SPA Memory (KB)],
SUM(pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC;

Consulta para pesquisar cache do plano para consultas com concessões de memória concluídas

SELECT top 50 t.text, cp.objtype ,qp.query_plan, cp.usecounts, cp.size_in_bytes as [Bytes Used in Cache]
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist('declare namespace n="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //n:MemoryFractions') = 1
order by cp.size_in_bytes desc
OPTION (MAXDOP 1)

Consulta para mostrar os pedidos de memória

-- Consulta para mostrar os pedidos de memória atuais, subvenções e plano de execução para cada sessão ativa
-- Isso mostra memória concedido e pediu para Atualmente sessões ativas no nível de instância
-- Este pode ser utilizado em um script para capturar informação ao longo de um período de tempo.

SELECT mg.session_id, mg.requested_memory_kb, mg.granted_memory_kb, mg.used_memory_kb, t.text, qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

Consulta para identificar objetos que estão tomando a maior parte do que a memória em Tampão Pool.

-- Consulta para identificar objetos que estão tomando a maior parte do que a memória em Tampão Pool.
-- Isso é só para o contexto do banco de dados atual. Por favor, prefixo <USE DBNAME> como por sua exigência

SELECT TOP 25
 DB_NAME(bd.database_id) as DBNAME,
 obj.[name] as [Object Name],
 sysobj.type_desc as [Object Type],
 i.[name]   as [Index Name],
 i.[type_desc] as [Index Type],
 count(*)AS Buffered_Page_Count ,
 count(*) * 8192 / (1024 * 1024) as Buffer_MB,
 bd.page_type as [Page Type] -- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd
    INNER JOIN
    (
        SELECT object_name(object_id) AS name
            ,index_id ,allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.hobt_id
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name  
            ,index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p
                ON au.container_id = p.hobt_id
                    AND au.type = 2
    ) AS obj
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
LEFT JOIN sys.objects sysobj on i.object_id = sysobj.object_id
WHERE database_id = DB_ID()
and sysobj.type not in ('S','IT')
GROUP BY DB_NAME(bd.database_id), obj.name, obj.index_id , i.[name],i.[type_desc],bd.page_type,sysobj.type_desc
ORDER BY Buffered_Page_Count DESC