Find Same Columns Across All Tables

Posted: February 26, 2009 in SQL Server 2005

To find a column name across all tables in a database, open a new query window and execute the following line of code:

-- Get a particular column name from all tables
SELECT   [SysObjects].[name]   AS [TableName]
        ,[SysColumns].[name]   AS [ColumnName]
        ,[SysTypes].[name]     AS [ColumnDataType]
        ,[SysColumns].[length] AS [ColumnLength]
FROM     [SysObjects]
         INNER JOIN [SysColumns] ON [SysObjects].[id]  = [SysColumns].[id]
         INNER JOIN [SysTypes]   ON [SysTypes].[xtype] = [SysColumns].[xtype]
WHERE    [SysObjects].[type] = 'U'
AND      [SysColumns].[name] LIKE '%%'
ORDER BY [SysObjects].[name] 
        ,[SysColumns].[name];
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