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/
Go to Top of Page

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/

Go to Top of Page

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

Go to Top of Page

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



Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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?

Go to Top of Page

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'

Go to Top of Page

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'



Go to Top of Page

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 [? ]
Go to Top of Page
   

- Advertisement -