How to search every field in the database

Recently I had an issue where one of my solution files got corrupted and I couldn’t remove it from SharePoint.

I used stsadm –o enumsolutions to find out what was happening and noticed that the solutions were there but only the ID of the file was there and no other information so I couldn’t retract or delete the solution file.

I was left with the cardinal sin of SharePoint going into the database and poking around. I didn’t know where about this information was held so I used a little trick of searching all the GUID fields in the database to see where it lived.

First bit I needed to do was find all the fields in the database that are GUID types and also what tables they were in

So I used this quick query to do that

SELECT syscolumns.name AS ColumnName,sysobjects.name AS TableName
FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
WHERE sysobjects.type='U' AND syscolumns.xtype=36

The syscolumns.xtype is type of column and you can get a list of all column data types by running

SELECT xtype, name FROM systypes

Which on SQL Server 2008 returns the following

34    image
35    text
36    uniqueidentifier
40    date
41    time
42    datetime2
43    datetimeoffset
48    tinyint
52    smallint
56    int
58    smalldatetime
59    real
60    money
61    datetime
62    float
98    sql_variant
99    ntext
104    bit
106    decimal
108    numeric
122    smallmoney
127    bigint
240    hierarchyid
240    geometry
240    geography
165    varbinary
167    varchar
173    binary
175    char
189    timestamp
231    nvarchar
239    nchar
241    xml
231    sysname

Now that I had my query to find all the specific fields in every table in the database I still needed to search for my GUID

I used part of my previous post’s to use a cursor and do my search

DECLARE @strSQL VARCHAR(5000)
DECLARE @Table VARCHAR(200)
DECLARE @Column VARCHAR(200)

DECLARE RowCounter CURSOR FOR
SELECT syscolumns.name AS ColumnName,sysobjects.name AS TableName
FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
WHERE sysobjects.type='U' AND syscolumns.xtype=36

OPEN RowCounter

FETCH NEXT FROM RowCounter
INTO @Column,@Table

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @strSQL = 'SELECT [' + @Column +  '] FROM [' + @Table + '] WHERE [' + @Column + '] = ''61c93a97-3989-4823-888d-1d68b5674350'''

PRINT 'Checking ' + @Column + ' in ' + @table

EXEC(@STRSQL)

FETCH NEXT FROM RowCounter
INTO @Column,@Table

END
CLOSE RowCounter
DEALLOCATE RowCounter

This allowed me to find the offending GUID which was in the Objects table in the Config database.