You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table has a timestamp column.

To do this, pass the table’s object ID as the first argument, and TableHasTimestamp as the second argument. The function returns a 1 or a 0 depending on whether or not it has a timestamp column.

A return value of 1 means that the table does have a timestamp column, and a value of 0 means that doesn’t.

This also works for columns that have been defined as rowversion (timestamp is the deprecated synonym for rowversion).

Example 1 – A timestamp Column

Here’s a quick example to demonstrate on a table with a timestamp column.

USE Test_timestamp;
SELECT OBJECTPROPERTY(OBJECT_ID('Pet'), 'TableHasTimestamp') AS [TableHasTimestamp];

Result:

+---------------------+
| TableHasTimestamp   |
|---------------------|
| 1                   |
+---------------------+

In this case, the Test_timestamp database has a table with the ID provided, and it has a timestamp column.

I use the OBJECT_ID() function to retrieve the table’s ID based on its name.

Example 2 – A rowversion Column

As mentioned, this method also works if you’ve used rowversion to define the column.

For example, if I create a table as follows:

CREATE TABLE Owner (
  OwnerId int PRIMARY KEY, 
  OwnerName varchar(255), 
  RowVersion rowversion
  );

And then run a check on it:

SELECT OBJECTPROPERTY(OBJECT_ID('Owner'), 'TableHasTimestamp') AS [TableHasTimestamp];

Result:

+---------------------+
| TableHasTimestamp   |
|---------------------|
| 1                   |
+---------------------+

I notice that, although Microsoft’s documentation states that timestamp is the synonym of rowversion, as of this writing, creating a column as rowversion on my SQL Server 2017 system actually makes it appear as a timestamp column. If I look at the type after the column has been created, it’s displayed as a timestamp column.

SELECT 
  OBJECT_NAME(object_id) AS [Table],
  name AS [Column],
  TYPE_NAME(system_type_id) AS [Type]
FROM sys.columns
WHERE OBJECT_NAME(object_id) = 'Owner';

Result:

+---------+------------+-----------+
| Table   | Column     | Type      |
|---------+------------+-----------|
| Owner   | OwnerId    | int       |
| Owner   | OwnerName  | varchar   |
| Owner   | RowVersion | timestamp |
+---------+------------+-----------+

Example 3 – Tables Without a TIMESTAMP Column

Here’s what happens when the table doesn’t have a timestamp column.

USE WideWorldImportersDW;
SELECT OBJECTPROPERTY(OBJECT_ID('Dimension.City'), 'TableHasTimestamp') AS [TableHasTimestamp];

Result:

+---------------------+
| TableHasTimestamp   |
|---------------------|
| 0                   |
+---------------------+

In this case, the object is a table but it doesn’t have a timestamp column.

Example 4 – When the Object Isn’t a Table

Here’s what happens when the database does contain an object with the ID, but that object isn’t even a table.

SELECT OBJECTPROPERTY(OBJECT_ID('Sequences.ReseedAllSequences'), 'TableHasTimestamp') AS [TableHasTimestamp];

Result:

+---------------------+
| TableHasTimestamp   |
|---------------------|
| NULL                |
+---------------------+

Example 5 – Object Doesn’t Exist

SQL Server assumes that the object ID is in the current database context. If you pass in an object ID from a different database, you’ll either get a NULL result or you’ll get incorrect results.

SELECT 
  OBJECTPROPERTY(OBJECT_ID('InvalidObject'), 'TableHasTimestamp') AS [InvalidObject],
  OBJECTPROPERTY(12345678, 'TableHasTimestamp') AS [12345678];

Result:

+-----------------+------------+
| InvalidObject   | 12345678   |
|-----------------+------------|
| NULL            | NULL       |
+-----------------+------------+

In this case the database contains no objects of that name or ID, and so I get a NULL result.

You’ll also get NULL on error or if you don’t have permission to view the object.