Created
May 14, 2015 20:32
-
-
Save ChadJPetersen/ca58c0a80685078f30ec to your computer and use it in GitHub Desktop.
Get all the schema, table, and column name combinations in the database. Where all values in that column are unique (Microsoft T-SQL / Transact SQL)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE #UniqueColumns ( | |
schemaName NVARCHAR(MAX), | |
tableName NVARCHAR(MAX), | |
columnName NVARCHAR(MAX) | |
); | |
DECLARE @unique BIT, | |
@checkSQL NVARCHAR(MAX), | |
@sName NVARCHAR(MAX), | |
@tName NVARCHAR(MAX), | |
@cName NVARCHAR(MAX); | |
DECLARE db_cursor CURSOR | |
FOR | |
( | |
SELECT SCHEMA_NAME(schema_id) AS schema_name, | |
t.NAME AS table_name, | |
c.NAME AS column_name | |
FROM sys.tables AS t | |
INNER JOIN sys.columns c | |
ON t.OBJECT_ID = c.OBJECT_ID | |
INNER JOIN sys.sysindexes AS i | |
ON t.object_id = i.id | |
AND i.indid < 2 | |
WHERE i.rows > 0 | |
) | |
OPEN db_cursor | |
FETCH NEXT | |
FROM db_cursor | |
INTO @sName, | |
@tName, | |
@cName; | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
SELECT @checkSQL = 'SELECT @uniqueOut = | |
CASE WHEN ( | |
(SELECT COUNT( * ) FROM ' + @sName + '.' + @tName + ' ) = (SELECT COUNT( DISTINCT ' + @cName + ' ) FROM ' + @sName + '.' + @tName + ' ) | |
) | |
THEN | |
''true'' | |
ELSE | |
''false'' | |
END;' | |
EXEC sp_executesql @checkSQL, | |
N'@uniqueOut BIT OUTPUT', | |
@uniqueOut = @unique OUTPUT | |
IF @unique = 'true' | |
BEGIN | |
INSERT INTO #UniqueColumns ( | |
schemaName, | |
tableName, | |
columnName | |
) | |
VALUES ( | |
@sName, | |
@tName, | |
@cName | |
) | |
END | |
FETCH NEXT | |
FROM db_cursor | |
INTO @sName, | |
@tName, | |
@cName; | |
END | |
CLOSE db_cursor | |
DEALLOCATE db_cursor | |
SELECT schemaName AS TABLE_SCHEMA, | |
tableName AS TABLE_NAME, | |
columnName AS COLUMN_NAME | |
FROM #UniqueColumns; | |
DROP TABLE #UniqueColumns; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment