Search for tables with specific columnType

[engnouna]

Hi

I have a huge database with more than 100 tables.I want to know which tables contains a column of type tinyInt?
is it possible?

thanks

[techgnome]

maybe... what is the database type?

-tg

[engnouna]

It is SQL Server 2005 database

[techgnome]

you can build a query using sysobjects and syscolumns looking at the column type... you'll need to do some experimental queries to figure out the exact values... I used to have a query that returned all objects with their columns and types - unfortunately I no longer have access to it and haven't needed to build it in some time... but it's possible.

-tg

[abhijit]

Doesn't SQL Server 2005 have the INFORMATION_SCHEMA.COLUMNS view?

[TysonLPrice]

From Google:

SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName, t.name
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'tinyint' --you can change text to other datatypes
ORDER BY c.OBJECT_ID;