August 16, 2002 at 6:20 am
Hi everybody!
Is there a SP that can help me search for the content of a field in a DB. I have here a DB that was not developed by us, containing 150 tables, and I need to create a report. But I don't find in which tables are the values that I'm looking for.
So it would helpful for me to say: give me the tables where a field is called "employee" or give me the tables where the content of a field is equal with "01234"
Thanks a lot,
Durug
August 16, 2002 at 6:32 am
The first is rather easy. Take a look at the syscolumns table. I'm not at a system where I have access to a SQL Server right now, so you'll need to check the accuracy of the query, but it goes something like this:
SELECT
USER_NAME(so.uid) Owner
, so.name TableName
FROM sysobjects so
JOIN syscolumns sc
ON so.id = sc.id
WHERE so.type = 'U'
AND sc.name = 'Employee'
In the second case, you'll need to write some dynamic SQL to generate where clauses for each table and column.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 16, 2002 at 8:31 am
thanks a lot
August 19, 2002 at 2:22 am
OK, now this is a script I wrote to find a value in DB without knowing where it might be. It is NOT optimized in any sense and because it uses a CURSOR many might have better ways to do it. But it works for me.
I hope it can give you somewhere to start...
---------
declare @tabname varchar(60)
declare @colname varchar(60)
declare @value varchar(20)
declare @rowcount int
DECLARE @SQLSTR VARCHAR(4000)
set @value = '1100EHCAZR080O07S' -- Value you are looking for.
declare TABCOL cursor fast_forward read_only for
select Tab.name Table_Name, Col.name Column_Name
from (dbo.sysobjects Tab
JOIN dbo.syscolumns Col ON Tab.id = Col.id
JOIN dbo.systypes dtype ON Col.xusertype = dtype.xusertype)
where tab.type = 'U'
and col.xusertype not in(select xusertype from systypes
where name in('bit', 'int', 'smallint','bigint','double','float','datetime','smalldatetime','timestamp','text','ntext','image'))
-- this section filters out tables that have no records in them, using the first index (PK)
AND TAB.NAME IN( SELECT DBO.SYSOBJECTS.NAME FROM DBO.SYSOBJECTS JOIN DBO.SYSINDEXES
ON DBO.SYSOBJECTS.ID = DBO.SYSINDEXES.ID
WHERE DBO.SYSINDEXES.INDID IN(0,1,255)
AND DBO.SYSINDEXES.ROWS > 0)
order by TAB.NAME
open TABCOL
fetch from tabcol into @tabname, @colname
while (@@fetch_status =0)
begin
SET @SQLSTR = 'declare @rowcount int
select @rowcount = count ('+@colname+') from '+@tabname+ ' where '+@colname+' like ''%'+@value+'%'';
if @rowcount <> 0
--this will select the record from the table where it has found your value.
BEGIN
print ''table: '+ @tabname + ' Column: '+@colname+'''
SELECT '+@COLNAME+', * FROM '+@TABNAME+' WHERE '+@COLNAME+' LIKE ''%'+@VALUE+'%'';
END'
EXEC(@SQLSTR)
fetch next from tabcol into @tabname, @colname
end
close tabcol
deallocate tabcol
go
--------
If anyone has a better way to do this please post it.
Thanks
Keith
"It makes me want to run around the Server Room in a Super-Hero Costume"
"It makes me want to run around the Server Room in a Super-Hero Costume"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply