July 28, 2009 at 1:57 pm
Hi,
This applies to several servers, but the solution will run on one server at a time. On any given target server I have several databases for test and QA. Most of the databases are copies of production data and share the same structure. There are a handful of "other" databases on these servers. I want to determine (using a single query; run one time) which databases contain a specific column. This will allow me to exclude "other" databases from a query that I must run.
I've spent hours trying to identify a join of information_schema objects and sys. system "tables" and cannot find the solution.
We're running SQL 2005 SP3.
Any suggestions are much appreciated.
Thanks,
July 28, 2009 at 2:38 pm
do you want the existence of table(as in your subject heading) or a column (as stated in body)?
in any case this should get you what you need
create table #tmp(
databasename varchar(100),
tablename varchar(100),
columnname varchar(100))
declare @db varchar(50)
declare @name varchar(50)
declare @tbl bit
declare @sql nvarchar(max)
set @name = 'tally' -- name of either table or column
set @tbl = 1 -- if searchjing table then 1, if column then 0
DECLARE _findcol CURSOR
FOR SELECT name FROM sys.databases where database_id > 4
-- Open the cursor.
OPEN _findcol
-- Loop through the partitions.
WHILE (1=1)
BEGIN
FETCH NEXT FROM _findcol
INTO @db
IF @@FETCH_STATUS < 0
BREAK
IF @tbl = 1
begin
set @sql ='
insert into #tmp(databasename,tablename)
select '''+@db+''',name as tablename from '+@db+'.sys.tables where name = '''+@name+'''
'
--print @sql
exec sp_executesql @sql
end
else
begin
set @sql ='
insert into #tmp(databasename,tablename,columnname)
select '''+@db+''' as databasename,a.name as tablename,b.name as columnname from '+@db+'.sys.tables a
inner join sys.columns b
on a.object_id = b.object_id
where b.name = '''+@name+'''
'
--print @sql
exec sp_executesql @sql
end
END
CLOSE _findcol
DEALLOCATE _findcol
select * from #tmp
drop table #tmp
July 28, 2009 at 3:07 pm
I appreciate your time and effort!!! This is what I needed. I was closer that I thought, but I just couldn't get there.
Again, Thank You.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply