Last active
December 10, 2021 21:08
-
-
Save TimothyK/457d0e58d5f7f40b9cc4bc894cc9156d to your computer and use it in GitHub Desktop.
Revisions
-
TimothyK revised this gist
Dec 10, 2021 . 2 changed files with 28 additions and 9 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -22,8 +22,15 @@ <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 @@ -46,8 +53,11 @@ While (1=1) 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 This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,7 +1,13 @@ --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 @@ -24,8 +30,11 @@ While (1=1) 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 -
Dec 9, 2021 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,62 @@ <?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 Declare @Id uniqueidentifier = '$Id$' --Search for this Guid --Set @Id = '' 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 = ''' + CAST(@Id As nvarchar(36)) + ''') Select ''' + @SchemaName + ''' As SchemaName, ''' + @TableName + ''' As TableName, * From ' + @SchemaName + '.[' + @TableName + '] Where Id = ''' + CAST(@Id As nvarchar(36)) + '''' exec sp_executesql @sql End Close curIdColumns Deallocate curIdColumns GO ]]> </Code> </Snippet> </CodeSnippet> </CodeSnippets> This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,35 @@ --Search all tables for an Id Declare @Id uniqueidentifier = '00000000-0000-0000-0000-000000000000' --Search for this Guid --Set @Id = '' 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 = ''' + CAST(@Id As nvarchar(36)) + ''') Select ''' + @SchemaName + ''' As SchemaName, ''' + @TableName + ''' As TableName, * From ' + @SchemaName + '.[' + @TableName + '] Where Id = ''' + CAST(@Id As nvarchar(36)) + '''' exec sp_executesql @sql End Close curIdColumns Deallocate curIdColumns GO