SQL SERVER – Identifying Column Data Type of uniqueidentifier without Querying System Tables
- by pinaldave
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)
Filed under: PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology
