Search all rows in the database for an Id

FindById.snippet

<?xml version="1.0" encoding="utf-8" ?> 
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet"> 
<CodeSnippet Format="1.0.0"> 
<Header>
<Title>id - Search All Tables for an Id</Title>
<Shortcut>id<!-- Not supported by MS SSMS yet --></Shortcut>
<Description>Search tables for a row</Description>
<Author>Timothy Klenke</Author> 
<SnippetTypes><SnippetType>Expansion</SnippetType></SnippetTypes>
</Header>

<Snippet>
<Declarations>

<Literal>
 <ID>Id</ID>
 <ToolTip>Id</ToolTip>
 <Default>00000000-0000-0000-0000-000000000000</Default>
</Literal>

</Declarations>
<Code Language="SQL">
<![CDATA[
--Search all tables for an Id

IF OBJECT_ID('tempdb..#Ids') IS NOT NULL 
	DROP TABLE #Ids

Create Table #Ids (Value uniqueidentifier Not Null Primary Key)

Insert Into #Ids(Value)		--Ids to Search for
Values ('$Id$')
	--, ('$end$')

Declare curIdColumns Cursor For
Select tbl.name As TableName, sch.name As SchemaName
From sys.tables tbl
	Inner Join sys.columns col On (col.object_id = tbl.object_id)
	Inner Join sys.schemas sch On (tbl.schema_id = sch.schema_id)
Where (col.name = 'Id') 
	And (col.system_type_id = 36)
	And (tbl.name Not Like '%_AUD')

Declare @sql nvarchar(max)
Declare @TableName sysname
Declare @SchemaName sysname

Open curIdColumns

While (1=1)
	Begin
	Fetch Next From curIdColumns 
	Into @TableName, @SchemaName
	If (@@Fetch_Status <> 0) break
	
	Set @sql = 'If Exists(Select Id From ' + @SchemaName + '.[' + @TableName + '] Where (Id In (Select id.Value From #Ids id)))
		Select ' + Case @SchemaName When 'dbo' Then '' Else '''' + @SchemaName + ''' As SchemaName, ' End
			+ '''' + @TableName + ''' As TableName, * 
		From ' + @SchemaName + '.[' + @TableName + '] 
		Where (Id In (Select id.Value From #Ids id))'
	
	exec sp_executesql @sql
	End

Close curIdColumns
Deallocate curIdColumns
GO
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

FindById.sql

--Search all tables for an Id

IF OBJECT_ID('tempdb..#Ids') IS NOT NULL 
	DROP TABLE #Ids

Create Table #Ids (Value uniqueidentifier Not Null Primary Key)

Insert Into #Ids(Value)		--Ids to Search for
Values ('00000000-0000-0000-0000-000000000000')
	--, ('')

Declare curIdColumns Cursor For
Select tbl.name As TableName, sch.name As SchemaName
From sys.tables tbl
	Inner Join sys.columns col On (col.object_id = tbl.object_id)
	Inner Join sys.schemas sch On (tbl.schema_id = sch.schema_id)
Where (col.name = 'Id') 
	And (col.system_type_id = 36)
	And (tbl.name Not Like '%_AUD')

Declare @sql nvarchar(max)
Declare @TableName sysname
Declare @SchemaName sysname

Open curIdColumns

While (1=1)
	Begin
	Fetch Next From curIdColumns 
	Into @TableName, @SchemaName
	If (@@Fetch_Status <> 0) break
	
	Set @sql = 'If Exists(Select Id From ' + @SchemaName + '.[' + @TableName + '] Where (Id In (Select id.Value From #Ids id)))
		Select ' + Case @SchemaName When 'dbo' Then '' Else '''' + @SchemaName + ''' As SchemaName, ' End
			+ '''' + @TableName + ''' As TableName, * 
		From ' + @SchemaName + '.[' + @TableName + '] 
		Where (Id In (Select id.Value From #Ids id))'
	
	exec sp_executesql @sql
	End

Close curIdColumns
Deallocate curIdColumns
GO