How to Find a GUID in whole SQL database (Script)

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