Just recently, while working on a script, I came across a situation where I had to check where the values (data type GUID) in a column of a specific table in my database were coming from. My first instinct was to check the foreign key relation on that column. But, as soon as I checked for the foreign keys associated with my table, I realized that there weren't any foreign key relations for that specific columns. I got confused. I wanted to know where the values were referenced from. Then it dawned upon me that I can simply search for the GUID in the database and get to know where else these values exist. That way, I'll know where else these values exist in the database. I searched the web and found some solutions but then, I decided to challenge myself to make my own utility script. So, here it goes:
USE YourDBName
GO
DECLARE @search nvarchar(100) = 'A37CC634-5289-413E-AD78-00191416284C'
DECLARE @tablename NVARCHAR(100)
DECLARE @columnname NVARCHAR(100)
DECLARE @query NVARCHAR(MAX)
DECLARE @rowcount INT
DECLARE @i INT = 1
DECLARE @count int
CREATE TABLE #alltables
(
N INT IDENTITY(1,1) NOT NULL,
columnname nvarchar(100) NOT NULL,
tablename nvarchar(100) NOT NULL
)
INSERT INTO #alltables
(
columnname,
tablename
)
SELECT c.name AS ColumnName,
'['+ s.name + '].[' + o.name + ']' AS TableName
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE system_type_id = (SELECT system_type_id
FROM sys.types
WHERE name = 'uniqueidentifier')
AND (s.name NOT LIKE 'sys%' AND o.name NOT LIKE 'sys%')
AND o.type = 'U'
SELECT @count = MAX(N)
FROM #alltables
WHILE (@i<=@count)
BEGIN
SELECT @tablename = tablename, @columnname = columnname
FROM #alltables
WHERE N = @i
SET @query = 'SELECT @result = COUNT(*) FROM ' + @tablename + ' WHERE ' + @columnname + ' = ' + ''''+ @search +''''
execute sp_executesql @query, N'@result int output', @result = @rowcount output
IF (@rowcount=1)
BEGIN
PRINT 'Exists in: '+ @tablename + ', ' + @columnname
END
SET @i = @i + 1
END
DROP TABLE #alltables
Result:
Comments
Post a Comment