February 15, 2007 at 9:29 am
Hi guys.
Im working with a database that contains over 500 tables. I have an asp.net web applications that performs various tasks against this database.
However from time to time I get an error message from the web application saying that it is unable to locate database 20202. In my tables there is column called 'System_DB' In this column is the value of the database you would intend to work with. I have ran a cursor to search and replace every value in the System_DB column with the value that I want. However from time to time I still get that error, suggesting that somewhere within 1 or more of my tables the value of 20202 still exists.
My question is how can I locate what table(s) and columns this value is coming.
many thanks.
February 15, 2007 at 12:21 pm
one question, can only the System_DB column have this database value in it or do you want to look through every column in the database for the 20202 value?
if the first, I would query the sys.columns table to see where the System_DB columns exists:
select o.name
from
sys.objects o
inner join sys.columns c
on o.object_id = c.object_id
where
c.name = 'system_db'
you could create a cursor to dynamically check the tables from the above resultset where system_db = 20202 and store that value in a temp table to return back to you.
hope this helps
February 15, 2007 at 12:26 pm
Thanks for your reply. The value could be in any column. but its more likely to be in the System_DB column. Since that is column that contains database ID's
February 15, 2007 at 1:01 pm
here is a script that will work but may take a while to process. i am sure that there is an easier way:
create table #tmp (table_name varchar(50), column_name varchar(50), num_records int)
declare @table_name varchar(50), @column_name varchar(50), @sql varchar(8000)
set @sql = ''
DECLARE table_cursor CURSOR FOR
SELECT o.name, c.name
FROM sys.objects o
inner join sys.columns c
on o.object_id = c.object_id
where
o.type <> 's' and c.system_type_id <> 61 --non system tables and non date fields
OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @table_name, @column_name
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- piece together the dynamic sql
select @sql = 'insert into #tmp select ''' + @table_name + ''', ''' + @column_name + ''', count(*) from ' + @table_name + ' where ' + @column_name + ' = ''20202'' '
--execute the sql statement
exec( @sql)
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM table_cursor
INTO @table_name, @column_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
--return the records where this value occurs
select * from #tmp where num_records <> 0
drop table #tmp
GO
February 16, 2007 at 5:12 am
Thanks, worked perfectly. Turned out the value was hard coded into a connection string that was stored in a table.
Many thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply