List all tables with an specific data type

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