SQL SERVER – Identifying Column Data Type of uniqueidentifier without Querying System Tables

I love interesting conversations with related to SQL Server. One of my friends Madhivanan always comes up with an interesting point of conversation. Here is one of the conversation between us. I am very confident this blog post will for sure enable you with some new knowledge.

Madhi: How do I know if any table has a uniqueidentifier column used in it?
Pinal:  I am sure you know that you can do it through some DMV or catalogue views.
Madhi: I know that but how can we do that without using DMV or catalogue views?
Pinal: Hm… what can I use?
Madhi: You can use table name.
Pinal: Easy, just say SELECT YourUniqueIdentCol FROM Table.
Madhi: Hold on, the question seems to be not clear to you – you do know the name of the column. The matter of the fact, you do not know if the table has uniqueidentifier column. Only information you have is table name.
Pinal: Madhi, this seems like you are changing the question when I am close to answer.
Madhi: Well, are you clear now? Let me say it again – How do I know if any table has a uniqueidentifier column and what is its value without using any DMV or System Catalogues? Only information you know is table name and you are allowed to return any kind of error if table does not have uniqueidentifier column.
Pinal: Do you know the answer?
Madhi: Yes. I just wanted to test your knowledge about SQL.
Pinal: I will have to think. Let me accept I do not know it right away. Can you share the answer please?
Madhi: I won! Here it goes!
Pinal: When I have friends like you – who needs enemies?
Madhi: (laughter which did not stop for a minute).

CREATE TABLE t
(
GuidCol UNIQUEIDENTIFIER DEFAULT newsequentialid() ROWGUIDCOL,
data VARCHAR(60)
)
INSERT INTO t (data) SELECT 'test'
INSERT INTO t (data) SELECT 'test1'
SELECT $rowguid FROM t
DROP TABLE t

This is indeed very interesting to me. Please note that this is not the optimal way and there will be many other ways to retrieve uniqueidentifier name and value. What I learned from this was if I am in a rush to check if the table has uniqueidentifier and I do not know the name of the same, I can use SELECT TOP (1) $rowguid and quickly know the name of the column. I can later use the same columnname in my query.

Madhi did teach me this new trick. Did you know this? What are other ways to get the check uniqueidentifier column existence in a database?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

21 thoughts on “SQL SERVER – Identifying Column Data Type of uniqueidentifier without Querying System Tables

  1. Two comments” First of all, you don’t need to insert any data to find out the name of the $rowguid column.

    Second, the question asked how you can know if a given table has ANY uniqueidentifier column. The proposed answer is not a complete solution. If there is a $rowguid column then the solution works, but it’s possible for a table to have a uniqueidentifier column without it being a $rowguid column, so if you run your script above and omit the word ROWGUIDCOL, then you’ll get an error. Even though the table has a uniqueidentifier column, you can’t identify it with $rowguid unless it happens to be a ROWGUIDCOL.

  2. Hmmm, I just thought of a third problem with the solution. What if the table has a nullable uniqueidentifier column? Can you find it? It can’t be ROWGUIDCOL if it’s nullable.

    Anyway, I’m sure the original puzzle was intended to feature the use of $rowguid and it does that nicely. It’s just that it doesn’t quite cover all of the bases.

  3. Similarly you can find identity column also using $IDENTITY,
    Declare @t TABLE (
    GuidCol UNIQUEIDENTIFIER DEFAULT newsequentialid() ROWGUIDCOL,
    IdentityCol int identity(1,1),
    data VARCHAR(60) )

    INSERT INTO @t (data) SELECT ‘test’
    INSERT INTO @t (data) SELECT ‘test1′

    SELECT $rowguid,$IDENTITY FROM @t

  4. We have used ‘ROWGUIDCOL’ column property in some tables so we can use it in one of the routines. When we retrieve ‘$rowguid’, database engine pick the column which has ‘ROWGUIDCOL’ column property. In a table, we cannot use this property for two columns – it is same like Identity column.

    If you try to create table with 2 columns having ‘ROWGUIDCOL’ column property then SQL throws following error:
    Msg 8196, Level 16, State 1, Line 1
    Duplicate column specified as ROWGUIDCOL.

    If I have a table without ‘ROWGUIDCOL’ column property then (without using DMV or system catalog – same question)?

  5. The sysjobs table has job_id as uniqueidentifier data type. But If I select that with $rowguid… it thorws an error! ..

    Msg 207, Level 16, State 1, Line 1
    Invalid column name ‘$rowguid’.

    I am not clear with this one… :-(
    I am working in SQL server 2005.

    Thanks.

  6. After noting the weaknesses in the proposed $rowguid solution, I decided to take up the challenge as it was originally posed. I didn’t fare any better in my attempt, but knowing how much Pinal enjoys unconventional solutions to his puzzles, I thought I’d offer this approach. As you’ll see, both of my queries offer solutions, but the second one is still a work in progress:

    First of all, my approach has a few weaknesses although they are different from the $rowguid approach. My approach won’t work on an empty table or on uniqueidentifier columns that contain only nulls, and it could give a false (but maybe acceptable) result if the table has a varchar or similar “string” column that contains only guids converted to strings but which are not actually defined as uniqueidentifier column in the table. Another HUGE weakness is that my approach converts the whole table to xml, so doing this on any table with very much data will consume a lot of memory and will take a while to process.

    With those caveats, and using a slightly modified version of Pinal’s sample table to demonstrate some of these issues, I have two versions of my solution which work on a table with no $rowguid column defined:

    The general idea is to use XML and CTEs to do the following:
    1) Convert the whole table including column names and data to an XML variable named @TableData
    2) Use successive CTEs to shred the xml
    3) Rule out all columns that can’t be Guids because they contain data that is not like @GuidPattern
    4) select the ColumnName and Content from all rows that contain only strings that can be converted to uniqueidentifiers

    My first query below accomplishes this much, but then I decided I wanted to try using a dynamic pivot table to reconstruct the results so that the original column names and data were in the query results. With that in mind, I reworked my original query to include a RowId column so that I could correctly re-associate the original rows. The second query is pretty much the same as the first except that it includes the original Row numbers as RowId. I got this far and ran out of time (gotta go to work some time ;-} ). But I figured I’d post my second query anyway in case it piques anyone’s interest in completing the dynamic pivot table part of it.

    Here are the two queries:
    First, my version of a solution to Pinal’s original puzzle:

    USE tempdb
    GO
    SET NOCOUNT ON;

    CREATE TABLE t
    (
    GuidNull UNIQUEIDENTIFIER,
    GuidCol UNIQUEIDENTIFIER,
    GuidKey UNIQUEIDENTIFIER DEFAULT newsequentialid(),
    data VARCHAR(60)
    )
    INSERT INTO t (GuidCol, data) SELECT null, ‘test0′
    INSERT INTO t (GuidCol, data) SELECT newid(), ‘test1′
    INSERT INTO t (GuidCol, data) SELECT newid(), newid() – Guid data in a varchar column
    INSERT INTO t (GuidCol, data) SELECT newid(), ”

    SELECT * FROM t

    DECLARE @TableData XML = CAST((SELECT * FROM t FOR XML PATH, ROOT(‘MyTableData’) ) AS XML)

    DECLARE @GuidPattern VARCHAR(260) =
    REPLICATE(‘[0-9A-F]’, 8) + ‘-‘ +
    REPLICATE(‘[0-9A-F]’, 4) + ‘-‘ +
    REPLICATE(‘[0-9A-F]’, 4) + ‘-‘ +
    REPLICATE(‘[0-9A-F]’, 4) + ‘-‘ +
    REPLICATE(‘[0-9A-F]’, 12)

    ;WITH NonNullColumnData AS
    (
    SELECT CAST(AllRows.[column].query(‘local-name(.)’) AS sysname ) AS ColumnName,
    AllRows.[column].value(‘.’, ‘varchar(MAX)’) AS Content
    FROM @TableData.nodes(‘/MyTableData/row’) TableData([xmlRow])
    CROSS APPLY TableData.[xmlRow].nodes(‘./*’) AllRows([column])
    )
    , NonGuidColumns AS (
    SELECT DISTINCT ColumnName
    FROM NonNullColumnData
    WHERE CONTENT NOT LIKE @GuidPattern
    )
    SELECT ColumnName,
    CAST(Content AS UNIQUEIDENTIFIER) AS Content
    FROM NonNullColumnData
    WHERE NOT EXISTS ( SELECT * FROM NonGuidColumns WHERE NonGuidColumns.ColumnName = NonNullColumnData.ColumnName )
    ORDER BY ColumnName,
    Content

    DROP TABLE t
    GO

    … and second, the beginning of my pivot table variation.

    USE tempdb
    GO
    SET NOCOUNT ON;

    CREATE TABLE t
    (
    GuidNull UNIQUEIDENTIFIER,
    GuidCol UNIQUEIDENTIFIER,
    GuidKey UNIQUEIDENTIFIER DEFAULT newsequentialid(),
    data VARCHAR(60)
    )
    INSERT INTO t (GuidCol, data) SELECT null, ‘test0′
    INSERT INTO t (GuidCol, data) SELECT newid(), ‘test1′
    INSERT INTO t (GuidCol, data) SELECT newid(), newid() – Guid data in a varchar column
    INSERT INTO t (GuidCol, data) SELECT newid(), ”

    SELECT * FROM t

    DECLARE @TableData XML = CAST((SELECT * FROM t FOR XML PATH, ROOT(‘MyTableData’) ) AS XML)

    DECLARE @GuidPattern VARCHAR(260) =
    REPLICATE(‘[0-9A-F]’, 8) + ‘-‘ +
    REPLICATE(‘[0-9A-F]’, 4) + ‘-‘ +
    REPLICATE(‘[0-9A-F]’, 4) + ‘-‘ +
    REPLICATE(‘[0-9A-F]’, 4) + ‘-‘ +
    REPLICATE(‘[0-9A-F]’, 12)

    ;WITH TableData AS
    (
    SELECT ROW_NUMBER() OVER ( PARTITION BY (SELECT 0) ORDER BY (SELECT 0)) AS RowId,
    TableData.[xmlRow].query(‘.’) AS XmlRow
    FROM @TableData.nodes(‘/MyTableData/row’) TableData([xmlRow])
    )
    , NonNullColumnData AS
    (
    SELECT RowId,
    CAST(AllRows.[column].query(‘local-name(.)’) AS sysname ) AS ColumnName,
    AllRows.[column].value(‘.’, ‘varchar(MAX)’) AS Content
    FROM TableData
    CROSS APPLY TableData.XmlRow.nodes(‘./row/*’) AllRows([column])
    )
    , NonGuidColumns AS (
    SELECT DISTINCT ColumnName
    FROM NonNullColumnData
    WHERE CONTENT NOT LIKE @GuidPattern
    )
    , GuidData as (
    SELECT RowId,
    ColumnName,
    CAST(Content AS UNIQUEIDENTIFIER) AS Content
    FROM NonNullColumnData
    WHERE NOT EXISTS ( SELECT * FROM NonGuidColumns WHERE NonGuidColumns.ColumnName = NonNullColumnData.ColumnName )
    )
    SELECT RowId,
    ColumnName,
    Content
    /* Bonus TODO: replace the rest of this query with a dynamic Pivot Table query that results in a variable number of columns with the same names and values as the non-null Guid columns in [t] */
    FROM GuidData
    ORDER BY RowId,
    ColumnName,
    Content

    DROP TABLE t
    GO

    Enjoy,
    Dave

  7. Hi pinal sir,

    Not a complete answer but near to that.
    Select the table name and click alt+F1.

    SQL server displays table column name and type.

    Regard$
    Chirag Satasiya

  8. Pingback: SQL SERVER – Follow up – Usage of $rowguid and $IDENTITY « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051 | SQL Server Journey with SQL Authority

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

    • However, ROWGUID contraint can be dropped as:
      ALTER TABLE MyTable
      ALTER COLUMN ID
      DROP ROWGUIDCOL

      Is it fine???
      OR please suggest any other..
      Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s