For script guru''s
-
I have a database with nearly 1000 tables. Several of the tables have a specific column...I'll call it a and some of those columns have a specfic value b.
So I want a list of tables in the database that have column a and have the specific value b that I am looking for. Is this difficult?
Thanks for any help.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
Yes an no. Depends on how clean yor answer needs to be. Quick, possibly look at using sp_MSForEachTable
exec sp_MSForEachTable 'SELECT ''?'' TableName FROM ? WHERE ColA = ValA'
-
-
Hi!! Fizz... looking for "Scripts Guru's"... smart title indeed
... now I 've to prove that am one of them..... (but I really dont thing so!!) 
Seriously... now,
You can create a sp to do the job. I can give u a tip... though this belongs to a programming section
:CREATE PROCEDURE SearchTables
AS
BEGIN
Declare @TableName varchar(128)
Declare @SQLCommand varchar(256)
Declare Cursor1 Cursor Static ForWard_Only For
select ob.name
FROM sysobjects ob INNER JOIN syscolumns col ON ob.id = col.id
WHERE ob.type = 'U'
AND col.name = 'a'
Open Cursor1
Fetch Next From Cursor1 Into @TableName
While @@Fetch_status = 0
Begin
Select @SQLCommand = 'declare @out varchar(128)
Select @out='''+@TableName+''' from '+ ltrim(rtrim(@TableName)) + ' where a='b'
if @out is not null select @out'
exec( @SQLCommand)
Fetch Next From Cursor1 Into @TableName
End -- While Fetch
Close Cursor1
Deallocate Cursor1
END
GO
------------
When you 've got a hammer, everything starts to look like a nail... -
Thanks a lot! Problem solved.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply