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 (https://blog.sqlauthority.com)
18 Comments. Leave new
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.
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.
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
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)?
loved your question and answer and your cute fight on SQL SERVER.
Thanks Pinal for this post
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.
If the table does not have any guid column, you will get this error
This is really very interesting
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
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
I want to know that, when we have identity column then why do we need uniqueidentifier column?
Is there any easy way to drop ROWGUID column from a table?
However, ROWGUID contraint can be dropped as:
ALTER TABLE MyTable
ALTER COLUMN ID
DROP ROWGUIDCOL
Is it fine???
OR please suggest any other..
Thanks
Thanks for the useful post…
Hi Pinal, If we know the table name, we can query
select rowguidcol from t
if table t dont have rowguidcol column, it will throw error.
we can try with this query
select rowguidcol from t
If we dont have rowguidcol column in the table t then it will fire error, else it will give result of UNIQUEIDENTIFIER column value