April 23, 2003 at 10:14 am
I would like to find the word "collect" in the database. I don't know which table it is in. Is there a way to do a search for a string in the database and have it return the names of the tables, fields where that string was found.
April 24, 2003 at 3:44 am
A weird request, but one that could be solved writing some dynamic SQL.
1. Iterate through the list of tables in the database that contain "text" type fields. Probably best declaring a cursor on 'sysobjects' selecting name and id where the xtype = 'U'. You could do it all in one by linking across to 'syscolumns'. I played a little and came up with :-
SELECT TOP 100 PERCENT dbo.sysobjects.name AS Table_Name, dbo.syscolumns.name AS Column_Name, dbo.syscolumns.typestat,
dbo.syscolumns.type
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id
WHERE (dbo.sysobjects.xtype = 'U') AND (dbo.syscolumns.typestat IN (0, 2, 3)) AND (dbo.syscolumns.length > 6) AND (dbo.syscolumns.type = 35 OR
dbo.syscolumns.type = 39)
ORDER BY dbo.sysobjects.name, dbo.syscolumns.name
2. For each table retrieve the list of "text" type columns and build a WHERE Clause of the form :-
FIELD_1 Like '%Collect%"
AND FIELD_2 Like '%Collect%"
AND FIELD_3 Like '%Collect%"
etc..
3. Construct a SELECT statement for each table which is of the form :-
SELECT @l_Count = ISNULL(COUNT(1), 0)
FROM {table_name}
WHERE FIELD_1 Like '%Collect%"
AND FIELD_2 Like '%Collect%"
AND FIELD_3 Like '%Collect%"
You can do this by building up a string and using the EXECUTE command.
Then test the count :-
IF @l_Count > 0
BEGIN
PRINT 'Table with Collect = ' + @l_Table
END
Or something like that.
Definitely look at "sysobjects" and "syscolumns" as a starter for 10.
Good luck.
April 24, 2003 at 3:47 am
Another little thing, you will have to play/read up on all the possible values for type, xtype etc to ensure you have the correct "text" type fields.
Here's a useful view that I use to get table and column lists, that I wack through an access report.
SELECT TOP 100 PERCENT dbo.sysobjects.name AS table_name, dbo.syscolumns.name AS column_name, dbo.syscolumns.colid AS column_position,
dbo.systypes.name AS data_type, systypes_1.name AS base_type, dbo.syscolumns.length, dbo.sysindexkeys.indid AS index_id,
dbo.sysindexkeys.keyno AS key_no, CONVERT(varchar(2000), sysproperties_1.[value]) AS table_descr, CONVERT(varchar(2000),
sysproperties_2.[value]) AS column_descr, dbo.sysindexkeys.colid, dbo.sysindexes.[first], dbo.sysindexes.status, dbo.sysindexes.indid,
dbo.syscolumns.isnullable
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xusertype = dbo.systypes.xusertype INNER JOIN
dbo.systypes systypes_1 ON dbo.systypes.xtype = systypes_1.xusertype INNER JOIN
dbo.sysindexes ON dbo.sysobjects.id = dbo.sysindexes.id LEFT OUTER JOIN
dbo.sysindexkeys ON dbo.sysindexes.indid = dbo.sysindexkeys.indid AND dbo.syscolumns.colid = dbo.sysindexkeys.colid AND
dbo.syscolumns.id = dbo.sysindexkeys.id LEFT OUTER JOIN
dbo.sysproperties sysproperties_1 ON dbo.sysobjects.id = sysproperties_1.id LEFT OUTER JOIN
dbo.sysproperties sysproperties_2 ON dbo.syscolumns.colid = sysproperties_2.smallid AND dbo.syscolumns.id = sysproperties_2.id
WHERE (dbo.sysobjects.name LIKE N'tbl%') AND (sysproperties_1.smallid = 0) AND (dbo.sysindexes.status = 18450) OR
(dbo.sysobjects.name LIKE N'tbl%') AND (sysproperties_1.smallid IS NULL) AND (dbo.sysindexes.status = 18450)
ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid
It's quite handy coz I link it to retrieve the extended properties of the columns. ie. Descriptions.
Again good luck.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply