August 13, 2010 at 10:24 am
Hi
I'm trying to write a script that will get the table names that have %doc% in from a table and then do a select against these tables for a docid.
declare @db_table_name varchar(30)
declare @docid int
DECLARE APPLICATION_CURSOR FOR
SELECT @DB_TABLE_NAME
FROM APPLICATIONS
WHERE DB_TABLE_NAME LIKE '%DOC%'
while @@fetch_status = 0
FETCH NEXT FROM APPLICATION_CURSOR
SELECT * FROM @DB_TABLE_NAME WHERE DOCID = @DOCID
if @@fetch_status <> 0
Print 'No more tables'
close cursor
deallocate cursor
Please can you advice what's wrong.
August 13, 2010 at 10:27 am
You need to use dynamic sql to do this.
Is this something you need to implement permanantly into the system or just something to help you find something you "lost" in the db?
August 13, 2010 at 10:33 am
Here's a quick demo. Run this query. Then copy the results of the query and paste it back into ssms and run that query.
declare @docid int
SET @docid = 1298
SELECT 'SELECT [' + T.name + '] As TableName, * FROM dbo.[' + T.name + '] WHERE DOCID = ' + CONVERT(VARCHAR(15), @docid) FROM sys.tables T
INNER JOIN sys.columns c on T.object_id = c.object_id
where T.name like '%doc%' and C.name = 'docid'
August 13, 2010 at 1:04 pm
Hi thanks will try.
Just need a tool to help me and others locate lost docid amongst different tables.
August 13, 2010 at 1:10 pm
Ok, just make sure that you don't put this into production. Or that if you do that all docids are indexed AND that you warn everybody that this is a very intensive process on the DB. And that you actually scan only the tables that might bring relevant info.
I never had to put this in the hands of every day users. I was always able to leave it to the IT bosses or someone who understood the pitfalls of using that.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply