Find all Columns with uniqueidentifier data type (GUID)

This week we continue with a series of UDFs that I've been writing as I've been programming replication. When SQL Server thinks that there is a chance that modified rows from a replica might have a chance of making their way back to update the publication, I insists in there being a RowGUID column, which must have a uniqueidentifier type. This happens with merge replication and can also happen with Transactional and Snapshot replication.

This week's UDF, identifies all the columns in your database that have the uniqueidentifier type. If the parameters is set to 1 it also only returns the column if it's the rowguid column for the table.

Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.udf_Tbl_GUIDColumnsTAB (

    @OnlyRowGuidCol BIT = 0 -- Only if it's a RowGuidCol

) RETURNS TABLE
/*
* Returns the columns in the database that have uniqueidentifier
* type.  Uniqueidentifier is a GUID.  If the @OnlyRowGuidCol 
* parameter is set, only those columns that are the RowGUID 
* columns for the row are returned. 
*
* Example:
select * from udf_Tbl_GUIDColumnsTAB(default)
*      
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as T-SQL UDF of Week Newsletter Vol 2 #18 04/20/04 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
*/
AS RETURN 
 
SELECT TOP 100 PERCENT WITH TIES
       o.name as [Table_Name]
     , c.name as [Column_Name]
     , COLUMNPROPERTY(o.id, c.name,'IsRowGuidCol') [IsRowGuidCol]
	FROM syscolumns c
		inner join sysobjects o
             on o.id = c.id 			
    WHERE c.xtype=36
        and (@OnlyRowGUIDCol=0 
            or COLUMNPROPERTY(o.id, c.name, 'IsRowGuidCol') = 1)
		and  OBJECTPROPERTY(o.id, N'IsUserTable') = 1
    ORDER BY o.Name, c.Name
GO


GRANT SELECT on dbo.udf_Tbl_GUIDColumnsTAB TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Here are a few examples from the database in which I'm developing replication:

select * from udf_Tbl_GUIDColumnsTAB(default)

(Results)
Table_Name                    Column_Name          IsRowGuidCol 
----------------------------- -------------------- ------------ 
AppApplication                msrepl_tran_version             0 
AppApplication                rowguid                         1 
AppConfiguration              msrepl_tran_version             0 
AppConfiguration              rowguid                         1 
AppFile                       msrepl_tran_version             0 
AppMessage                    msrepl_tran_version             0 
AppMessage                    rowguid                         1 
AppMessageParameter           msrepl_tran_version             0 
AppMessageParameter           rowguid                         1 
AppReportCategory             msrepl_tran_version             0 
AppReportDefinition           msrepl_tran_version             0 
AppReportDefinition           rowguid                         1 
AppReportParameter            msrepl_tran_version             0 
AppReportParameter            rowguid                         1 
AppReportParameterDataType    msrepl_tran_version             0 
AppReportParameterDataType    rowguid                         1 
AppSecurityGroup              msrepl_tran_version             0 
AppSecurityGroup              rowguid                         1 
AppSecurityGroup2Privilege    msrepl_tran_version             0 
AppSecurityGroup2Privilege    rowguid                         1 
AppSecurityGroup2User         msrepl_tran_version             0 
AppSecurityGroup2User         rowguid                         1 
AppSecurityHistory            msrepl_tran_version             0 
AppSecurityHistory            rowguid                         1 
AppSecurityPrivilege          msrepl_tran_version             0 
AppSecurityPrivilege          rowguid                         1 
AppSession                    SessionGUID                     0 
Company                       rowguid                         1 
OrderLineType                 msrepl_tran_version             0 
OrderLineType                 rowguid                         1 
PriceBookType                 msrepl_tran_version             0 
PriceBookType                 rowguid                         1 
PriceLevelCode                msrepl_tran_version             0 
ProductGroupCode              msrepl_tran_version             0 
ProductGroupCode              rowguid                         1 
ProductLineCode               msrepl_tran_version             0 
ProductLineCode               rowguid                         1 
ProductLineMatrix             msrepl_tran_version             0 
ProductLineMatrix             rowguid                         1 
ProductType                   msrepl_tran_version             0 
ProductType                   rowguid                         1 
State                         msrepl_tran_version             0 
State                         rowguid                         1 

As you can see there are many tables that have more than one uniqueidentifier column. How unique does a row have to be?

The problem is exactly why I wrote this UDF in the first place. the columns named rowguid were the ones that I had added during development. The columns named msrepl_tran_version were added by SQL Server as I created a publication. I believe it created them because the existing uniqueidentifier columns didn't have the RowGUID designation. This is a problem that I'll have to fix. You'll read more on this in the coming weeks.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick