Find all your timestamp Columns

I've been programming replication over the last couple of weeks. It's pretty amazing what happens but the setup is very very particular about exactly how everything is configured. Over the next few weeks I'll be posting several UDFs that I've developed in the course of implementing replication. There'll be a few tips-and-tricks as well, so stay tuned.

One of the problems that I encountered was that the definition of user defined types can easily be removed as the tables are copied into the snapshot. This includes the timestamp type which gets converted to BINARY(8). There are ways to prevent this but it's taken a lot of slow trial and error to resolve all the issues.

Along the way I decided to take a look at which columns had timestamp types. The result is the function udf_Tbl_Timestamp. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE function dbo.udf_Tbl_TimeStampColumnsTAB (

) RETURNS TABLE
/*
* Returns all the timestamp columns in user tables in 
* the database.
*
* Example:
select * from udf_Tbl_TimeStampColumnsTAB()
*      
* © 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 #17 04/13/04 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN 

select o.[name] as [Table_Name]
     , c.[name] as [Column_Name]
	from syscolumns c
		inner join sysobjects o
                      on o.id = c.id 
          where c.xtype=189
		and  OBJECTPROPERTY(o.id, N'IsUserTable') = 1
GO


GRANT SELECT on dbo.udf_Tbl_TimeStampColumnsTAB TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

The function is easy to write once you discover that syscolumns.xtype is 189 for timestamp columns. Everything else follows. Here's an example on one of my development databases:

SELECT * from udf_Tbl_TimestampColumnsTAB()


(Results)
Table_Name              Column_Name   
----------------------- --------------
AppMessage              RowVrsn
AppReportDefinition     RowVrsn
AppReportParameter      RowVrsn
BoilerPlateMasterText   RowVrsn
Company                 EditStamp
OrderLineType           RowVrsn
PriceBookType           RowVrsn
ProductGroupCode        RowVrsn
ProductLineCode         RowVrsn
ProductLineMatrix       RowVrsn
ProductType             RowVrsn

As you can see, I usually call my timestamp columns RowVrsn. The Books-on-line tells us that the timestamp data type may be changed to be in alignment with the SQL-92 standard which uses timestamp to mean the same as the datetime data type. Rowversion is the name that will take the place of the current timestamp.

Of course, someone got in to the schema and added an Editstamp column. In the end I removed it and most of the other RowVrsn columns as unnecessary. That allowed me to sidestep the problems that I had been experiencing with replication.

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