Musings on code and life

Find Record Query

I put together a SQL query that can find a given record in a database given you know the Id, but don't know which table it goes with. I hope I'll never need this again, but you never know.


DECLARE @Id varchar(MAX) = '4311'

--REF: http://stackoverflow.com/questions/1525672/determine-a-tables-primary-key-using-tsql

DECLARE @SchemaName varchar(MAX), @TableName varchar(MAX), @ColumnName varchar(MAX)
DECLARE keys CURSOR FOR  
SELECT
schema_name(ta.schema_id) SchemaName
,ta.name TableName
,col.name ColumnName
FROM sys.tables ta
INNER JOIN sys.indexes ind ON ind.object_id = ta.object_id
INNER JOIN sys.index_columns indcol ON indcol.object_id = ta.object_id AND indcol.index_id = ind.index_id
INNER JOIN sys.columns col ON col.object_id = ta.object_id AND col.column_id = indcol.column_id
WHERE ind.is_primary_key = 1
ORDER BY ta.name, indcol.key_ordinal  

OPEN keys   
FETCH NEXT FROM keys INTO @SchemaName, @TableName, @ColumnName
WHILE @@FETCH_STATUS = 0 BEGIN   

DECLARE @SQL nvarchar(MAX) = '
SELECT * FROM [' + @SchemaName + '].[' + @TableName + '] WHERE [' + @ColumnName + ']=' + @Id + '
'
EXEC sp_executesql @SQL

FETCH NEXT FROM keys INTO @SchemaName, @TableName, @ColumnName
END   
CLOSE keys   
DEALLOCATE keys