List all tables with an specific datatype

When working with replications, migration of data etc we often needs to know if a table has a datatype that we can´t handle. E.g a timestamp can not be moved with the content, then we need to exclude that column. This little query lists all tables in an database that has in this case a timestamp. Change the number of the datatype to reflect any other datatype, e.g datetime, varchar etc. can also be used to list tables with the hated guid datatype ( the number for an guid is: 36.

select o.name as TableName, c.name as Columnname from syscolumns c,sysobjects o
where c.id = o.id
and c.xtype = 189
order by o.name asc

To get all iformation about all tables all columns datatypes we can use this code:

SELECT o.name AS TableName, c.name AS Columnname, c.xtype, T.name
FROM syscolumns AS c INNER JOIN
sysobjects AS o ON c.id = o.id INNER JOIN
systypes AS T ON c.xtype = T.xtype
–WHERE (c.xtype = 36)
ORDER BY o.name

Remove the –WHERE (c.xtype = 36)
to search for only a specific datatype.
Can also be changed to Where t.name =’timestamp’ the code then looks like this:

SELECT o.name AS TableName, c.name AS Columnname, c.xtype, T.name
FROM syscolumns AS c INNER JOIN
sysobjects AS o ON c.id = o.id INNER JOIN
systypes AS T ON c.xtype = T.xtype
WHERE T.name =’timestamp’
ORDER BY o.name

Från Khan sql dba – mcitp www.addarr.com

Advertisements

2 thoughts on “List all tables with an specific datatype

  1. Rosa 08/17/2014 at 22:25 Reply

    An answer from an expert! Thanks for contbiruting.

    Like

  2. Drew 09/09/2014 at 12:41 Reply

    I am really pleased to read this web site posts which contains plenty of useful data, thanks for providing these data.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: