List all columns with a specific data type in a database

Found this script and it’s a great addition to your cookbook!

An example for AdventureWorksDW2012:
Columns-with-a-specific-data-type-sql-server


SELECT
s.name AS SchemaName,
ts.name AS TableName,
c.name AS ColumnName,
SCHEMA_NAME(t.schema_id) AS DataTypeSchema,
t.name AS DataTypeName,
t.Is_user_defined,
t.Is_assembly_type,
c.Is_nullable,
c.Max_length,
c.Precision,
c.Scale
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = ts.schema_id
WHERE t.name = 'money'
ORDER BY 1,2,3








Advertisements

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