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

Consulta para localizar o uso de Buffers por cada banco de dados

-– Consulta para localizar o uso de Buffers por cada banco de dados
-– Cada uma dessas páginas estão presentes no buffer cache, o que significa que são páginas IN_RAM.

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters
   WHERE RTRIM([object_name]) LIKE‘%Buffer Manager’
   AND counter_name = ‘Total Pages’;

;WITH BufCount AS
(
  SELECT
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [Database_Name] = CASE [database_id] WHEN 32767
       THEN‘MSSQL System Resource DB’
       ELSE DB_NAME([database_id]) END,
   [Database_ID],
   db_buffer_pages as [Buffer Count (8KB Pages)],
   [Buffer Size (MB)] = db_buffer_pages / 128,
   [Buffer Size (%)] = CONVERT(DECIMAL(6,3),
       db_buffer_pages * 100.0 / @total_buffer)
FROM BufCount
ORDER BY [Buffer Size (MB)] DESC;

Ultimas Querys Rodadas

Atenção: Se quiser mudar o período, verificar GETDATE()-1

SELECT        SQLTEXT.text, STATS.last_execution_time
FROM          sys.dm_exec_query_stats STATS
CROSS APPLY   sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT
WHERE         STATS.last_execution_time > GETDATE()-1
ORDER BY      STATS.last_execution_time DESC

Procura Palavra toda Base

Atenção: A consulta deve ser apontada para a base desejada.

DECLARE
    @search_string VARCHAR(100),
    @table_name SYSNAME,
    @table_id INT,
    @column_name SYSNAME,
    @sql_string VARCHAR(2000)
SET @search_string = 'Palavra_Busca'
DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)
    OPEN columns_cur
    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE ' + @column_name + ' LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
    EXECUTE(@sql_string)
    FETCH NEXT FROM columns_cur INTO @column_name
    END
    CLOSE columns_cur
    DEALLOCATE columns_cur
      FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur

Procura Coluna toda Base

Atenção: A consulta deve ser apontada para a base desejada.

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 '%Nome_da_coluna%'

ORDER BY schema_name, table_name;

Consumo MB por tabela

SELECT

                DB_NAME(db_id()) DatabaseName,

                Result.ObjectName,

                COUNT(*) AS cached_pages_count,

                index_id

 FROM sys.dm_os_buffer_descriptors A

INNER JOIN

(              SELECT

                                OBJECT_NAME(object_id) as ObjectName,

                                A.allocation_unit_id,

                                type_desc,

                                index_id,

                                rows

                FROM sys.allocation_units A, sys.partitions B

                WHERE A.container_id = B.hobt_id

                AND (A.type = 1 or A.type = 3)

                UNION ALL

                SELECT

                                OBJECT_NAME(object_id) as ObjectName,

                                allocation_unit_id,

                                type_desc,

                                index_id,

                                rows

                FROM sys.allocation_units AS au

                                INNER JOIN sys.partitions AS p

                                                ON au.container_id = p.partition_id

                                                AND au.type = 2

                                                ) as Result

On A.allocation_unit_id = Result.allocation_unit_id

WHERE database_id = db_id()

GROUP BY

                Result.ObjectName,

                index_id

ORDER BY cached_pages_count DESC

GO

Consumo MB por Base

Atenção: A consulta deve ser apontada para a base desejada.

WITH Consumo_Pool_Buffer
AS
(
                SELECT
                                Database_id,
                                BuffersPorPagina = COUNT_BIG(*)
                FROM sys.dm_os_buffer_descriptors
                GROUP BY database_id
)
SELECT
                Database_id as DatabaseID,
                CASE Database_id WHEN 32767
                                THEN 'Recurso interno do SQL SERVER'
                                ELSE DB_NAME(Database_id) END AS DatabaseName,
                BuffersPorPagina,
                (CONVERT(NUMERIC(10,2),BuffersPorPagina*8)/1024) AS BuffersPorMB
FROM Consumo_Pool_Buffer
ORDER BY BuffersPorPagina DESC, BuffersPorMB DESC
GO