-
Search a string throughout the database
Hello
I have a Database which contains like 1000 Tables.
I am not the designer of that DB.So I need to in which table and which column
that string exists. IS there a DBWIDE String search possible?
Thanks and Regards
-
There is no one statement to do that. You have write a stored procedure or script to loop though all tables, all columns and search it.
-
This statement will generate a bunch of sql statements and you could use to find the table that matches your search criteria
set quoted_identifier off
declare @searchstring varchar(100)
set @searchstring ='%sys%'
Select 'Select "'+object_name(id)+'" as tablename,count(*) as NoofRowsMatch from '+object_name(id) + ' where '+name +' like "'+@searchstring +'"'
from syscolumns where xtype in (175,239,99,231,35,167)
-
i used your string builder function and run the resulting string set and got this error
Invalid column name
I tried removing the quotes from the tablename but no luck
and since we select a text ie tablename which is not in the table it throws an error.
But it is a very cool idea to get all those tables details
in that way.
Thanks MAK
Once again you have proven you are a real specialist.
Last edited by vimalpercy; 03-08-2006 at 08:58 AM.
-
set quoted_identifier off
declare @searchstring varchar(100)
set @searchstring ='%sys%'
Select 'Select "'+object_name(id)+'" as tablename,count(*) as NoofRowsMatch from '+object_name(id) + ' where '+name +' like "'+@searchstring +'"'
from syscolumns where xtype in (175,239,99,231,35,167)
In this script
i generated the string and replaced the "" quote with single quote by search and replace method and then it worked.
-
you could use
set quoted_identifier off
before execute all the select statement
-
I tried to execute the script and it given me nearly 2000rows...
But i couldn't understand what exactly it is doing... can some one brief me this.... please
-
It generates sql statements to search specified string in every column of every table in the db.
-
/****************************************
SEARCH FOR A STRING THROUGHOUT A DATABASE
****************************************/
CREATE PROCEDURE db_search
@the_text NVARCHAR(100) = NULL
AS
SET NOCOUNT ON
SELECT @the_text = ISNULL(@the_text, '')
DECLARE @runner INT,
@limit INT,
@SQL NVARCHAR(1000),
@is_like TINYINT,
@SQLParams NVARCHAR(30)
DECLARE @mytbl TABLE (ident INT IDENTITY(1, 1),
tbl NVARCHAR(200) COLLATE database_default NULL,
col NVARCHAR(200) COLLATE database_default NULL,
is_like TINYINT NULL)
--Get columns of every table that contain character like, data types
INSERT INTO @mytbl(tbl, col)
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE LIKE '%char%'
OR DATA_TYPE LIKE '%text%'
OR DATA_TYPE LIKE '%uniqueidentifier%'
ORDER BY TABLE_NAME, ORDINAL_POSITION
--Initialize Variables
SELECT @SQLParams = '@res TINYINT OUTPUT', @the_text = '%'+@the_text+'%'
--Virtual Cursor
SELECT @runner = 1, @limit = ISNULL(MAX(ident), 0) -- ISNULL(),actually not needed
FROM @mytbl
--Run through temporary table records
WHILE @runner <= @limit
BEGIN
--Initialize Variables on every pass(build dynamic query)
SELECT @is_like = 0, @SQL = 'SELECT TOP 1 @res = 1 FROM ['+tbl+'] WHERE ['+col+'] LIKE '''+ @the_text +''''
FROM @mytbl
WHERE ident = @runner
--Execute dynamic query and get the output value
EXEC sp_executesql @SQL, @SQLParams,
@res = @is_like OUTPUT
--Set result to current record
UPDATE @mytbl
SET is_like = @is_like
WHERE ident = @runner
--Move to next record
SELECT @runner = @runner + 1
END
SELECT tbl AS TABLE_NAME, col AS COLUMN_NAME
FROM @mytbl
WHERE is_like = 1
--EOF--
--HTH--
Last edited by mikr0s; 03-12-2006 at 09:24 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|