As a SQL Server enthusiast, I always enjoy engaging in interesting conversations about SQL Server concepts and ideas. Recently, I had a conversation with my friend Madhivanan, which led to an intriguing question: How do we determine if a table has a uniqueidentifier column without using any DMV or System Catalogues?
Typically, we can use DMV (Dynamic Management Views) or catalogue views to retrieve information about a table’s columns. However, Madhivanan challenged me to find an alternative solution using only the table name and without any knowledge of the column name.
Initially, I suggested a simple query: SELECT YourUniqueIdentCol FROM Table. However, Madhivanan clarified that the question was not about knowing the column name, but rather about determining if the table has a uniqueidentifier column and retrieving its value.
After admitting that I didn’t know the answer right away, Madhivanan shared the solution with me:
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 solution involves creating a temporary table with a uniqueidentifier column and inserting some sample data. By selecting the $rowguid column from the table, we can determine if the table has a uniqueidentifier column and retrieve its value.
It’s important to note that this is not the most efficient or optimal way to check for a uniqueidentifier column. There are other methods and techniques available. However, this approach can be useful when you’re in a rush and don’t know the column name but need to quickly determine if a table has a uniqueidentifier column.
Learning this trick from Madhivanan was a valuable lesson for me. It’s always fascinating to discover new ways to solve problems in SQL Server. If you have any other methods or techniques for checking the existence of a uniqueidentifier column in a database, I would love to hear about them.
Thank you for reading!