How to search Database for a particular column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to search Database for a particular column?

Author

Topic

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-04-10 : 12:32:16

Is there a is script that can be ran to search all databases on a server for a particular column? I have a list of columns from a Oracle server and I need to search a SQL Server 2008, to see if the columns exist there too.

Any ideas would be much appreicated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-10 : 12:55:34

select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'column name goes here'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-04-10 : 13:04:50

Running this select on INFORMATION_SCHEMA.COLUMNS is that looking at ALL the databases on a particular server?

quote:
Originally posted by tkizer

select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'column name goes here'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-10 : 13:39:45

Please try this:



DECLARE @db_pattern sysname
DECLARE @column_name_pattern sysname

SET @db_pattern = '%' --%=all dbs
SET @column_name_pattern = 'your_col_name/pattern_here'

-----------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#columns_found') IS NOT NULL
    DROP TABLE #columns_found
CREATE TABLE #columns_found (
    db_name varchar(100),
    object_name varchar(100),
    name varchar(100),
    datatype varchar(30),
    length int,
    decimal_places tinyint
    )

DECLARE @sql nvarchar(4000)
SET @sql = '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
OR ''?'' NOT LIKE ''' + @db_pattern + '''
    RETURN
USE "?" --replace "" with []
INSERT INTO #columns_found ( 
    db_name, object_name, name, datatype, 
    length, decimal_places 
    )
SELECT 
    ''?'', OBJECT_NAME(c.object_id), c.name, t.name, 
    CASE WHEN t.precision = 0 THEN c.max_length ELSE NULL END,
    CASE WHEN t.precision = 0 THEN NULL ELSE c.scale END
FROM sys.columns c
INNER JOIN sys.types t ON
    t.user_type_id = c.user_type_id
WHERE
    OBJECTPROPERTY(c.object_id , ''IsUserTable'') = 1 AND
    c.name LIKE ''' + @column_name_pattern + '''
'

PRINT @sql

EXEC sp_MSforeachdb @sql

SELECT *
FROM #columns_found
ORDER BY
    db_name, object_name, datatype, length, decimal_places

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-04-10 : 13:54:58

Your variable @db_pattern, does that take a list or a scheme name?

quote:

Originally posted by ScottPletcher

Please try this:



DECLARE @db_pattern sysname
DECLARE @column_name_pattern sysname

SET @db_pattern = '%' --%=all dbs
SET @column_name_pattern = 'your_col_name/pattern_here'

-----------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#columns_found') IS NOT NULL
    DROP TABLE #columns_found
CREATE TABLE #columns_found (
    db_name varchar(100),
    object_name varchar(100),
    name varchar(100),
    datatype varchar(30),
    length int,
    decimal_places tinyint
    )

DECLARE @sql nvarchar(4000)
SET @sql = '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
OR ''?'' NOT LIKE ''' + @db_pattern + '''
    RETURN
USE "?" --replace "" with []
INSERT INTO #columns_found ( 
    db_name, object_name, name, datatype, 
    length, decimal_places 
    )
SELECT 
    ''?'', OBJECT_NAME(c.object_id), c.name, t.name, 
    CASE WHEN t.precision = 0 THEN c.max_length ELSE NULL END,
    CASE WHEN t.precision = 0 THEN NULL ELSE c.scale END
FROM sys.columns c
INNER JOIN sys.types t ON
    t.user_type_id = c.user_type_id
WHERE
    OBJECTPROPERTY(c.object_id , ''IsUserTable'') = 1 AND
    c.name LIKE ''' + @column_name_pattern + '''
'

PRINT @sql

EXEC sp_MSforeachdb @sql

SELECT *
FROM #columns_found
ORDER BY
    db_name, object_name, datatype, length, decimal_places

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-04-11 : 04:11:00

You can make use of the result of this

select 'select * from '+name+'.INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = ''column name goes here''' from sys.databases

Madhivanan

Failing to plan is Planning to fail

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-11 : 13:07:42

quote:
Your variable @db_pattern, does that take a list or a scheme name?

No, @db_pattern only controls the database(s) to be searched.

Would you like to add a schema name(s) list and/or a table name(s) list to the code?

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-04-11 : 14:19:48

Well, I kind of looking to beable to put a column name in a variable and excute, and it would search all databases and their tables on a server for that entered column name.

quote:
Originally posted by ScottPletcher

quote:
Your variable @db_pattern, does that take a list or a scheme name?

No, @db_pattern only controls the database(s) to be searched.

Would you like to add a schema name(s) list and/or a table name(s) list to the code?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-11 : 14:22:01

That's exactly what my code does.

Just change the:
SET @column_name_pattern = ''
to be set to the column name you want to find.

For example, to find everywhere where "first_name" is used as column name, use this:

SET @column_name_pattern = 'first_name'

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-04-14 : 13:22:37

Scott when I run your code i get the following errors, as it's creating a list of my DBs.


IF '?' IN ('master', 'model', 'msdb', 'tempdb')
OR '?' NOT LIKE '%'
    RETURN
USE "?" --replace "" with []
INSERT INTO #columns_found ( 
    db_name, object_name, name, datatype, 
    length, decimal_places 
    )
SELECT 
    '?', OBJECT_NAME(c.object_id), c.name, t.name, 
    CASE WHEN t.precision = 0 THEN c.max_length ELSE NULL END,
    CASE WHEN t.precision = 0 THEN NULL ELSE c.scale END
FROM sys.columns c
INNER JOIN sys.types t ON
    t.user_type_id = c.user_type_id
WHERE
    OBJECTPROPERTY(c.object_id , 'IsUserTable') = 1 AND
    c.name LIKE '%AREA%'
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'master'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'tempdb'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'msdb'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'ReportServer$RPT'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'ReportServer$RPTTempDB'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbCSI'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbCustomerSurvey'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbDestMapping'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_EDW'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbEDWConfig'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbEmployee'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbERT'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbIEX'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbMyInfo'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbRemedy'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbWFM_BSC'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbReportSummary'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbSalesMarketing'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbServerAdmin'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbSmartSync'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbTools'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbSSISMetrics'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbOCC'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbCustomerData'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbACSS'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbACD'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbCorrectiveAction'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbDevSandbox'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_ACSS'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_Customer_Survey'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_RCA'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbExceptionManagement'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbInetLog'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_Varollii'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbArchive_Test_compression'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbVZPortal'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbUserSupport'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_Lombardi'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_QMT'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_Staging'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_IEX'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_Witness'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbPerformanceLogs'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbOvertime'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbRMConfig'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_WFM'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_Avaya'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_Varollii_from_TSM'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_FUD'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_Admin'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_LiveVox'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_DBOSS'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_Cognos'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_CCP'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbExceptionWriter'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbMSTR_Metadata'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbMyInfo_Contractor'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_ICM'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_Employee'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbArchive_ExPRT_CFS'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_CACS'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbETL_SurveyMonkey'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbMessaging'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'dbExceptionManagement_PROD'.

quote:
Originally posted by ScottPletcher

That's exactly what my code does.

Just change the:
SET @column_name_pattern = ''
to be set to the column name you want to find.

For example, to find everywhere where "first_name" is used as column name, use this:

SET @column_name_pattern = 'first_name'

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-14 : 13:36:31

quote:
USE "?" --replace "" with []

You literally do need to replace the "" with [].

The code should be:

USE [? ]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources