Find a GUID (or anything) in ANY table in your database!

Share

[Updated 2015-03-12]

Do you ever run across an ID, such as a GUID or an integer (or anything else, for that matter) while stepping through code and aren’t quite sure which table it’s supposed to be for or need to know where it’s referenced anywhere in your database? I use GUIDs like they’re going out of style for a plethora of reasons, especially in my tables. EVERYTHING in my tables has a GUID to identify it. I use them as primary keys and as foreign keys. They’re computer generated, can be generated either on the db server, the web server, or even on the client and can be guaranteed to be unique in that table, or in an entire database, or in the entire universe, for that matter (try doing that with auto-incrementing integers!!!).

Anyway… I found myself needing to find an arbitrary GUID anywhere in the database and was doing a lot of manual querying. Finally, I decided that this could be EASILY automated by querying the database meta data for tables with GUID columns, then automatically querying those tables and columns for my GUID and output the results of each table and column the GUID was found in.

Below, is my solution: A stored procedure called FindGuidInAnyTable that takes a single parameter (a GUID) and searches the entire database for all occurrences of it. You could adapt this for any column type… not just GUIDs, BTW.

[Update 2015-03-13: Using the newer, friendlier system table names, and including the schema name as well]

ALTER procedure [dbo].[FindGuidInAnyTable]
(
 @FindThisGUID uniqueidentifier
) as

begin

 declare @TableSchema varchar(100)
 declare @TableName varchar(100)
 declare @NextColumn varchar(200)
 declare @Select varchar(1000)

 create table #ResultsTable
 (
 SchemaName varchar(100),
 TableName varchar(100),
 ColumnName varchar(100)
 )

 declare TableNameCursor cursor for
 select
 table_schema,
 table_name
 from
 INFORMATION_SCHEMA.tables
 where
 table_type = 'BASE TABLE'

 OPEN TableNameCursor

 FETCH NEXT FROM TableNameCursor INTO @TableSchema, @TableName

 WHILE (@@FETCH_STATUS <> -1) BEGIN

 IF (@@FETCH_STATUS <> -2) BEGIN

 declare ColumnNameCursor cursor for
 select distinct
 COLUMN_NAME
 from
 INFORMATION_SCHEMA.COLUMNS
 where
 TABLE_SCHEMA = @TableSchema and
 TABLE_NAME = @TableName and
 DATA_TYPE = 'uniqueidentifier'

 OPEN ColumnNameCursor

 FETCH NEXT FROM ColumnNameCursor INTO @NextColumn

 WHILE (@@FETCH_STATUS <> -1) BEGIN
 
 IF (@@FETCH_STATUS <> -2) BEGIN

 set @select = 'insert into #ResultsTable select ''' + @TableSchema + ''' as TableSchema, ''' + @TableName + ''' as TableName, ''' + @NextColumn + ''' as ColumnName from [' + @TableSchema + '].[' + @TableName + '] where [' + @NextColumn + '] = ''' + cast(@FindThisGUID as varchar(50))+ ''''
 print @select
 exec(@select)

 end
 
 FETCH NEXT FROM ColumnNameCursor INTO @NextColumn
 
 END

 CLOSE ColumnNameCursor
 DEALLOCATE ColumnNameCursor

 end

 FETCH NEXT FROM TableNameCursor INTO @TableSchema, @TableName

 END

 CLOSE TableNameCursor
 DEALLOCATE TableNameCursor

 select
 count(*) as Instances,
 SchemaName,
 TableName,
 ColumnName,
 @FindThisGUID as GuidFound
 from
 #ResultsTable
 group by
 SchemaName,
 TableName,
 ColumnName
 order by
 SchemaName,
 TableName,
 ColumnName

end

For MS SQL Server versions prior to SQL Server 2005, do this:

CREATE procedure [dbo].[FindGuidInAnyTable]
(
   @FindThisGUID uniqueidentifier
) as

begin

declare @TableName varchar(100)
declare @NextColumn varchar(200)
declare @Select     varchar(1000)

create table #ResultsTable
(
 TableName varchar(100),
 ColumnName varchar(100)
)

declare TableNameCursor cursor for
  select distinct
     o.name
  from
 syscolumns c,
 sysobjects o
  where
     c.id    = o.id and
     c.xtype = 36   and
     o.xtype = 'U'

OPEN TableNameCursor

FETCH NEXT FROM TableNameCursor INTO @TableName

WHILE (@@FETCH_STATUS &lt;> -1) BEGIN

  IF (@@FETCH_STATUS &lt;> -2) BEGIN

     declare ColumnNameCursor cursor for
        select distinct
           c.name
        from
 syscolumns c,
 sysobjects o
        where
           c.id    = o.id       and
           c.xtype = 36         and
           o.name  = @TableName and
           o.xtype = 'U'

     OPEN ColumnNameCursor

     FETCH NEXT FROM ColumnNameCursor INTO @NextColumn

     WHILE (@@FETCH_STATUS &lt;> -1) BEGIN
 
        IF (@@FETCH_STATUS &lt;> -2) BEGIN

           set @select = 'insert into #ResultsTable select ''' + @TableName + ''' as TableName, ''' + @NextColumn + ''' as ColumnName from [' + @TableName + '] where [' + @NextColumn + '] = ''' + cast(@FindThisGUID as varchar(50))+ ''''
           print @select
           exec(@select)

        end
 
        FETCH NEXT FROM ColumnNameCursor INTO @NextColumn
 
     END

     CLOSE      ColumnNameCursor
 DEALLOCATE ColumnNameCursor

  end

  FETCH NEXT FROM TableNameCursor INTO @TableName

END

CLOSE      TableNameCursor
DEALLOCATE TableNameCursor

select
  count(*) as Instances,
 TableName,
 ColumnName,
  @FindThisGUID as GuidFound
from
  #ResultsTable
group by
 TableName,
 ColumnName
order by
 TableName,
 ColumnName

end

Here’s how you use it:


exec FindGuidInAnyTable '34fdbfa2-cdbd-4f34-bd2e-1423063fb707'

Here are what the results look like in my database:

I put this stored procedure in all of my databases. It’s a real time saver!

Share

Web Service makes duplicate, incompatible types of individual types

Share

I had a tough time coming up with a title for this article. It’s hard to explain, exactly what problem I’m referring to is, and choosing key words to help people find this article who are looking for a solution to their problem will be even more difficult. Let me try to explain the problem:

I’ve got a web service. It has many classes and enumerations in it. Many classes contain members of some of the other classes. When I write an application that consumes the web service, some of those types end up as two seperate types. For example, suppose I have an enumeration like this:


public enum MyEnum
{
one,
two,
three
}

Suppose I have two classes like this:


public class MyFirstClass
{
public MyEnum TypeOfThing;
public string something;
}

public class MySecondClass
{
public MyEnum TypeOfOther;
public bool IsThisPlainEnough;
}

Then, in my consuming application, my proxy web class (which is auto generated by Visual Studio when you add a web reference), I’ll get TWO separate MyEnums, one with the name MyEnum and another with MyEnum1. Then, my two classes might come across like this:


public class MyFirstClass
{
public MyEnum TypeOfThing;
public string something;
}

public class MySecondClass
{
public MyEnum1 TypeOfOther;
public bool IsThisPlainEnough;
}

As you can see, it makes MyFirstClass.TypeOfThing incompatible with MySecondClass.TypeOfOther.

I haven’t researched this enough to determine exactly what’s causing this, but I do know something much more important… a solution!

Here’s what you do:

Add an XmlType attribute to the type that gets duplicated, like this:


[System.Xml.Serialization.XmlType(Namespace="MyNameSpace.MyEnum", TypeName="MyEnum")]
public enum MyEnum
{
one,
two,
three
}

Now, when I add a web reference to my class library, it generates ONLY ONE MyEnum and it makes MyFirstClass.TypeOfThing compatible with MySecondClass.TypeOfOther.

That’s it.

Share

Hibernation unavailable in Windows Vista

Share


I recently installed some Windows udpates on my Windows Vista Ultimate 32bit OS. When I came back up after the required reboot, I got the dialog box to the left from my PowerChute software (software that controls my APC battery backup). The text of the dialog reads:

“PowerChute has detected that hibernation support is not enabled on your computer. Unsaved data will not be saved following an extended power problem.

APC recommend that you enable hibernatoin support so that all application data is preserverd.”

Aside from the annoying grammatical error (“APC recommends“), it did not give any information on how to re-enable it. I searched all over Windows Vista to find something to turn it back on. It was just NOT available!! I thought the latest Windows Update just ripped it out of the OS (as Microsoft has done with many features in Vista that used to be in XP). When I googled the text in the dialog box, I got a whopping ONE result and it was on how to resolve the problem in Windows XP. So, I held my nose and went to the Windows Vista “Help & Support” option on my own computer and looked up “hibernation”. The first hit was “what happened to sleep and hibernation?”. Clicking on that link led me to a Microsoft web page explaining how to turn it back on. This is where Apple Computer gets all their amunition against Microsoft. Here’s the answer:

  1. Go to a command prompt. (Start menu, Programs, Accessories, right-click “Command Prompt”, choose “Run as administrator”, and enter your administrator password when prompted.)
  2. type: powercfg -h on
  3. Reboot.
  4. When it comes back up, go to control panel, power options.
  5. Click on “Change plan settings” under whichever power plan you have selected.
  6. Click on “Change advanced power settings”.
  7. Expand “Power buttons and lid”.
  8. Expand each sub-item and set them as you wish. You’ll notice that “hibernate” is now an available option.
Share