Geeks With Blogs
Patrice Calve Life's short, have fun

So...

You're trying to debug an application that is using a large (100> tables) database and for a reason or an other, you don't understand where this darn Id is coming from!

Here's a simple Sql Script that browses your database for this darn Id...

Just change the @Id = '' for the id you're looking for!

declare @id as varchar(50)
 
--        idToRemove.ToString()    ""    String
 
 
select @id = '2eda82d8-9dcb-dd11-965a-001279d8c645'
 
set nocount on
 
 
 
declare @template as varchar(5000)
 
set @template = 'declare @id as uniqueidentifier
select @id = ''' + @id + '''
if exists(select * from [<%=TableName%>] where <%=ColumnNamesToInclude%>)
    begin
        print ''<%=TableName%>''
        select * from [<%=TableName%>] where <%=ColumnNamesToInclude%>
    end'
 
 
declare @TableName as varchar(250)
 
DECLARE TAB_CURSOR CURSOR FOR
    SELECT TABLE_NAME as TableName 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'
    ORDER BY TableName
 
OPEN TAB_CURSOR
 
FETCH NEXT FROM TAB_CURSOR
    INTO @TableName
 
WHILE @@FETCH_STATUS=0
BEGIN
 
    DECLARE @sql as varchar(max)
    DECLARE @columnCount as int
    DECLARE @colName as varchar(500)
    DECLARE @columnDataType as varchar(50)
    DECLARE @delimiter as varchar(10)
    DECLARE @columnNamesToInclude as varchar(max)
 
--    print '--------------------------------------------------------'
--    print '-- '
--    print '-- ' + @tableName 
--    print '-- '
--    print '-- '
 
    set @colName = ''
    set @delimiter = ''
    set @columnNamesToInclude = ''
    set @columnCount = 0
 
    DECLARE COL_CURSOR CURSOR FOR
        select COLUMN_NAME as [columnName]
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE 
            TABLE_NAME = @tableName
            AND DATA_TYPE = 'uniqueidentifier'
        ORDER BY ORDINAL_POSITION
 
    OPEN COL_CURSOR
 
    FETCH NEXT FROM COL_CURSOR
    INTO @colName
 
    WHILE @@FETCH_STATUS=0
    BEGIN
        set @columnCount = @columnCount + 1
        set @columnNamesToInclude = @columnNamesToInclude + @delimiter + '[' + @colName + '] = @Id'
 
        set @delimiter = ' OR ' 
 
        FETCH NEXT FROM COL_CURSOR 
            INTO @colName
 
    END
 
    CLOSE COL_CURSOR 
    DEALLOCATE COL_CURSOR  
 
    if @columnCount > 0 
    BEGIN
        set @sql = @template
 
        set @sql = replace(@sql, '<%=TableName%>', @tableName)
        set @sql = replace(@sql, '<%=ColumnNamesToInclude%>', @columnNamesToInclude)
        set @sql = replace(@sql, '<%=ScriptDateTime%>', getdate())
 
        exec (@sql)
--        print ''
--        print ''
--        print @sql
--        print ''
--        print 'GO'
--        print ''
--        print ''
--        print ''
--        print ''
 
 
    END
 
    FETCH NEXT FROM TAB_CURSOR 
        INTO @tableName
 
END
 
CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR 

 

Posted on Thursday, May 28, 2009 2:29 PM | Back to top


Comments on this post: Simple Sql script for finding a Guid/Uniqueidentifier in your database

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Patrice Calvé | Powered by: GeeksWithBlogs.net