CODEWITHSUNDEEP
t-sqlsql-server-2000
DavidStein
DavidStein

Reputation: 3179

How can I get a list of tables in a database without a timestamp column?

How can I get a list of tables in a database without a timestamp column?

Any suggestions?

Upvotes: 5

Views: 1821

Answers (3)

gbn
gbn

Reputation: 432361

You could use OBJECTPROPERTY

SELECT
    name
FROM
    sys.objects
WHERE
    OBJECTPROPERTY(object_id, 'TableHasTimestamp') = 0 --null if not a table

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181340

Using INFORMATION SCHEMA views:

select * from INFORMATION_SCHEMA.TABLES T where NOT EXISTS 
  (
      select 1 
        from INFORMATION_SCHEMA.COLUMNS 
       where TABLE_CATALOG = T.TABLE_CATALOG
         and TABLE_SCHEMA = T.TABLE_SCHEMA
         and TABLE_NAME = T.TABLE_NAME
         and DATA_TYPE = 'timestamp' -- or the literal representing timestamp data type
  )

Upvotes: 5

OMG Ponies
OMG Ponies

Reputation: 332641

Using SYS.TABLES/SYS.COLUMNS:

SELECT name FROM SYS.TABLES 
 WHERE object_id NOT IN (select object_id 
                           FROM SYS.COLUMNS
                          WHERE system_type_id = 189)

Upvotes: 3

Related Questions