Post a Comment On: Sergi Adamchuk's blog

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