February 4, 2008

Find all values with specified Guid in whole database

The following script looks through all tables and all columns (of Uniqueidentifier type) and outputs how many records with specified ID

DECLARE @ID UNIQUEIDENTIFIER
SELECT @ID='20b1d494-1e03-49af-918d-09a54cd6f792' -- Specify ID here


IF object_id('TempDB..#tables') IS NOT NULL DROP TABLE #tables
CREATE TABLE #tables
(
[Id] INT NOT NULL PRIMARY KEY,
[Name] SYSNAME NOT NULL
)
INSERT #tables
(
[Id],
[Name]
)
SELECT
[Id],
[Name]
FROM sysobjects
WHERE xtype = 'U'

IF object_id('TempDB..#columns') IS NOT NULL DROP TABLE #columns
CREATE TABLE #columns
(
[TableId] INT NOT NULL,
[Name] SYSNAME NOT NULL,
[Count] INT NULL
)

INSERT #columns
(
[TableId],
[Name]
)
SELECT
SC.[Id],
SC.[Name]
FROM
#tables T
JOIN
SYSCOLUMNS SC ON T.id = SC.id
WHERE sc.xtype = 36

DELETE #tables WHERE [Id] NOT IN (SELECT TableID FROM #Columns)

DECLARE @tableId INT, @nameColumn SYSNAME, @cmd VARCHAR(4000)
WHILE(EXISTS(SELECT * FROM #columns WHERE [Count] IS NULL))
BEGIN

SELECT TOP 1 @cmd = 'UPDATE R SET [Count] = (SELECT COUNT(*) FROM ['+T.[Name]+'] WHERE ['+C.[Name]+'] ='''+CONVERT(VARCHAR(36), @ID)+''') FROM #columns R WHERE R.[TableId] = '+CONVERT(VARCHAR(10),T.[Id])+' AND R.[Name] = '''+C.[Name]+''''
FROM
#tables T
JOIN
#columns C ON T.[Id] = C.TableId
WHERE
C.[Count] IS NULL

--PRINT @cmd

EXEC(@cmd)
END

SELECT
T.[Name] AS 'Table Name',
C.[Name] AS 'Column Name',
C.[Count] AS 'Records'
FROM
#tables T
JOIN
#columns C ON T.[Id] = C.TableId
WHERE
C.[Count] > 0

2 comments:

alexandroid said...

typo: "looks throw" --> "looks through"

Sergi said...

Thank you. Fixed.