Hitta unika nycklar i dina SQL Server tabeller

By | december 21, 2015
0 Comment

Alla som jobbar professionellt med databasutveckling vet hur viktigt det är att hålla koll på de unika nycklarna. Tyvärr händer det alltför ofta, särskilt inom Business Intelligence-system, att det inte finns någon information om vilka unika nycklar som gäller i den data vi får från olika källsystem. Ibland visar sig också att det som skulle vara en unik nyckel inte alls är unikt.

För att snabbt få en överblick över unika nycklar så har jag gjort ett enkelt SQL-script. Scriptet testar alla kombinationer av kolumner för att se om de fungerar som unik nyckel.

Scriptet har några parametrar som skall fyllas i:

  • @table_schema och @table_name. Fyll i schemat och namn på tabellen du vill undersöka här.
  • @skip_columns. Fyll i med en kommaseparerad lista över kolumner som inte skall undersökas. Det kan vara exempelvis metadatakolumner som du själv har lagt till.
  • @max_depth. Ange maximalt antal kolumner att kombinera. Om du sätter för högt värde kan scriptet ta för lång tid att köra.
-- Fyll i parametrar här
DECLARE	@table_schema AS VARCHAR(100) = 'dbo',
		@table_name AS VARCHAR(100) = 'Tabellnamn',
		@skip_columns AS VARCHAR(900) = 'skräpkolumn1,skräpkolumn2',
		@max_depth AS INT = 3;
-- Slut på parametrar

SELECT 
	Col = '[' + COLUMN_NAME + ']'
	,Pos = ORDINAL_POSITION
INTO #columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name AND TABLE_SCHEMA = @table_schema
AND (',' + @skip_columns + ',') NOT LIKE '%,' + COLUMN_NAME + ',%';

WITH cte AS
(
	SELECT
		Col = CAST(Col AS VARCHAR(900))
		,Pos
		,1 AS Depth
	FROM #columns

	UNION ALL

	SELECT 
		Col = CAST(cte.Col + ',' + c.Col AS VARCHAR(900))
		,c.Pos
		,cte.Depth + 1
	FROM cte
	INNER JOIN #columns AS c
	ON cte.Pos < c.Pos AND cte.Depth < @max_depth
)
SELECT Tuple = Col 
INTO #tuples
FROM cte;

DECLARE @sql AS NVARCHAR(MAX) = 'SELECT Tuple = ''*Total*'', Cnt = COUNT(*) FROM ' + @table_schema + '.' + @table_name + ' ';
SELECT @sql = @sql + 'UNION ALL SELECT ''' + Tuple + ''', COUNT(*) FROM (SELECT DISTINCT ' + Tuple + ' FROM ' + @table_schema + '.' + @table_name + ') AS t '
FROM #tuples;

CREATE TABLE #results
(
	Tuple VARCHAR(900) PRIMARY KEY,
	Cnt INT NOT NULL
);

INSERT INTO #results
EXEC sp_executesql @sql;

SELECT *
FROM #results
ORDER BY Cnt DESC, Tuple ASC;

DROP TABLE #columns;
DROP TABLE #tuples;
DROP TABLE #results;

Related Posts