January 18, 2019 at 5:25 am
I need to find a column in all DBs on multiple server since I don't know where this column is and I have this query I am running and it does run successfully but the DBname in the result set isn't correct.
DECLARE @command varchar(1000)
SELECT @command = 'USE ?
declare @DBNAme varchar(100)
Select @DBName = name from sys.databases
SELECT @DBName as DBName, t.name as TableName, c.name as ColumnName
from sys.tables t
Join sys.columns c on t.object_id = c.object_id
where c.name like ''%CDL%'''
EXEC sp_MSforeachdb @command
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 18, 2019 at 5:54 am
Syed_SQLDBA - Friday, January 18, 2019 5:25 AMI need to find a column in all DBs on multiple server since I don't know where this column is and I have this query I am running and it does run successfully but the DBname in the result set isn't correct.DECLARE @command varchar(1000)
SELECT @command = 'USE ?
declare @DBNAme varchar(100)
Select @DBName = name from sys.databases
SELECT @DBName as DBName, t.name as TableName, c.name as ColumnName
from sys.tables t
Join sys.columns c on t.object_id = c.object_id
where c.name like ''%CDL%'''EXEC sp_MSforeachdb @command
I'd just use DB_NAME() in the main query
"Select @DBName = name from sys.databases" will just set the variable to the last row that the query returns. So it will give you the same value no matter what database you run it on.
January 18, 2019 at 6:40 am
Syed_SQLDBA - Friday, January 18, 2019 5:25 AMI need to find a column in all DBs on multiple server since I don't know where this column is and I have this query I am running and it does run successfully but the DBname in the result set isn't correct.DECLARE @command varchar(1000)
SELECT @command = 'USE ?
declare @DBNAme varchar(100)
Select @DBName = name from sys.databases
SELECT @DBName as DBName, t.name as TableName, c.name as ColumnName
from sys.tables t
Join sys.columns c on t.object_id = c.object_id
where c.name like ''%CDL%'''EXEC sp_MSforeachdb @command
If you do use sp_MSforeachdb please be aware that it can skip databases for no apparent reason.
Thanks
January 18, 2019 at 7:13 am
This should work.
DECLARE @command varchar(1000)
SELECT @command = '
SELECT db_name() as DBName, t.name as TableName, c.name as ColumnName
from sys.tables t
Join ?.sys.columns c on t.object_id = c.object_id
where c.name like ''%CDL%'''
EXEC sp_MSforeachdb @command
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 18, 2019 at 8:20 am
The ? placeholder will be replaced by the database name, so just use it as a string literal.
EXEC sys.ms_foreachdb '
SELECT ''?'' AS DBName, t.name as TableName, c.name as ColumnName
FROM [?].sys.tables t
INNER JOIN [?].sys.columns c ON t.object_id = c.object_id
where c.name like ''%CDL%''';
January 18, 2019 at 8:33 am
If you're going to run this in a multi-server window, you may not want separate results for each database.
Also, I deal with servers with different collations so at times the results are not compatible.
I usually use this format to get one result per server, and avoid collation issues.
DECLARE @command VARCHAR(MAX);
SELECT @command = STUFF((SELECT REPLACE(' UNION ALL SELECT ''?'' COLLATE SQL_Latin1_General_CP1_CI_AS as DBName,
t.name COLLATE SQL_Latin1_General_CP1_CI_AS as TableName,
c.name COLLATE SQL_Latin1_General_CP1_CI_AS as ColumnName
FROM [?].sys.tables t
INNER JOIN [?].sys.columns c ON t.object_id = c.object_id
WHERE c.name LIKE ''%CDL%''', '?', name)
FROM sys.databases
WHERE state_desc = 'ONLINE'
FOR XML PATH(''),TYPE).value('.','varchar(max)'), 1, 10, '');
EXEC(@command);
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply