SQL View to search in all columns of all tables

Often, I need to search a column name in a database… but I’ve not the table name. If you are like me, this SQL View can help you.

IF EXISTS(SELECT * FROM sys.views WHERE name = 'AllColumns')
DROP VIEW AllColumns


SELECT sys.tables.name AS "TableName",
sys.columns.name AS "ColumnName",
sys.systypes.name AS "ColumnType",
sys.columns.max_length AS "ColumnSize",
sys.columns.is_nullable AS "ColumnNullable",
sys.columns.scale AS "ColumnScale",
sys.columns.collation_name AS "ColumnCollation"
FROM sys.tables
INNER JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id
INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id

Tagged with:
Posted in SQL Server, Tips and others

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

Follow me
   RSS Feed    Twitter    Linked In

Write you email address to subscribe at this blog and to receive new posts by mail.

Join 611 other followers

Who I am?

%d bloggers like this: