Sql Query Util

sql util

--============Todas as tabelas que possuem a respectiva coluna============--
DECLARE @ColumnName VARCHAR(20) = 'Column Name'

SELECT T.name AS Tabela, C.name AS Coluna
FROM sys.sysobjects AS T (NOLOCK) 
	INNER JOIN sys.all_columns AS C (NOLOCK) ON T.id = C.object_id AND T.XTYPE = 'U' 
WHERE C.NAME LIKE '%' + @ColumnName + '%'
ORDER BY T.name ASC

--============Ocorrência da palavra em Procedures, Functions ou Views============--
DECLARE @ObjectName VARCHAR(20) = 'Object Name'

SELECT A.NAME, A.XTYPE, B.TEXT
FROM sys.sysobjects A (nolock)
	JOIN sys.syscomments B (nolock) ON A.ID = B.ID
WHERE B.TEXT LIKE '%' + @ObjectName + '%'
	AND A.XTYPE = 'P'
ORDER BY A.NAME

--============Quantidade de registros duplicados na tabela============--
SELECT NmCidade, Count(*) 
FROM [dbo].[Empresa]
GROUP BY NmCidade
HAVING Count(*) > 1

--============Quantidade de registros duplicados na tabela============--
--criando a colecao com o total de linhas
WITH CTE AS(
   SELECT [id_correspondente], [id_regional], 
	RN = ROW_NUMBER()OVER(PARTITION BY [id_correspondente], [id_regional] ORDER BY [id_correspondente], [id_regional])
   FROM [DE_SAFI_TST].[dbo].[SAFI_Correspondente_Regional]
)

--SELECT * FROM cte
--deletando da tabela quem tem mais de 1 registro
DELETE 
FROM CTE 
WHERE RN > 1

--============Lista de tabelas/views/procedures com as colunas e seus respectivos detalhes============--
DECLARE @ObjectName VARCHAR(20) = 'Object Name'
DECLARE @ObjectType CHAR(1) = 'P' --U = TABELA  |  V = VIEW  |  P = PROCEDURE

SELECT	O.name AS OBJETO,
		C.name AS COLUNA,
		T.name AS TIPO,
		C.[length] AS TAMANHO,
		CASE C.isnullable 
			WHEN 1 THEN 'SIM'
			ELSE 'NÃO' 
		END AS ACEITANULO

FROM SYSOBJECTS AS O 
	INNER JOIN SYSCOLUMNS AS C	ON (O.ID = C.ID) 
	INNER JOIN SYSTYPES AS T	ON (C.USERTYPE = T.USERTYPE) 

WHERE O.XTYPE = @ObjectType
	AND O.NAME = @ObjectName
ORDER BY O.NAME

--============Tabela temporária============--
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL 
	DROP TABLE #MyTempTable

CREATE TABLE #MyTempTable (
	IDLote INT,
	Descricao VARCHAR(50),
)

INSERT INTO #MyTempTable (IDLote, Descricao)
	SELECT	IDLote, Descricao
	FROM [cob].[Lote]

SELECT *
FROM #MyTempTable

DROP TABLE #MyTempTable

--============Caracter coringa===========--
SELECT * 
FROM CentroCusto
WHERE CodCusto LIKE '_____' 

--============Tamanho e número de registros===========--
SELECT t.NAME AS Entidade,
	p.rows AS Registros,
	SUM(a.total_pages) * 8 AS EspacoTotalKB,
	SUM(a.used_pages) * 8 AS EspacoUsadoKB,
	(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacoNaoUsadoKB

FROM sys.tables t
	INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
	INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
	INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
	LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255

GROUP BY t.Name, s.Name, p.Rows
ORDER BY Registros DESC

--============Apagar todas as tabelas de um esquema===========--
DECLARE @SqlQuery	NVARCHAR(MAX) = ''
DECLARE @Schema		VARCHAR(20)   = 'Schema Name'

SELECT @SqlQuery += 'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @Schema
	AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

PRINT @SqlQuery
--EXEC Sp_executesql @SqlQuery

--============Transações abertas===========--
SELECT * FROM sys.sysprocesses WHERE open_tran = 1


--============ IS NULL Condicional ===========--
DECLARE @tipo SMALLINT = 1

SELECT ID
FROM MyTable T
WHERE ((@tipo = 1 AND T.MyColumn IS NULL) 
	OR (@tipo = 2 AND T.MyColumn IS NOT NULL))