Searching entire database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Searching entire database

Author

Topic

j0shua
Starting Member

40 Posts

Posted - 2009-02-17 : 22:33:04

Is is possible to search the entire database in all tables or a group of tables for a specific uniqueidentifier? I need to select a specific uniqueidentifier and set all to a new uniqueidentifier.

is that possible?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-02-17 : 23:06:28

A little code generation will get you started:

select 'SELECT * FROM ' + quotename(table_name) + ' WHERE ' + quotename(column_name) + '=''11223344-5566-7788-99AA-BBCCDDEEFF00'''
from information_schema.columns c 
where data_type='uniqueidentifier' and exists(select * from information_schema.tables where table_name=c.table_name and table_type='BASE TABLE')

Change the GUID value to the one you're searching for. Run this and copy the results into another query window and run them. That will select the rows from tables that contain that GUID.

I'll leave it to you figure out how to generate the UPDATE statements.

j0shua
Starting Member

40 Posts

Posted - 2009-02-17 : 23:45:09

wow thanks! this is a good lead!

j0shua
Starting Member

40 Posts

Posted - 2009-02-18 : 04:45:00

i have a question. I have applied your code to my code. and it works!! however, is there a way to check if the resulting command execute(@searchCommand) exist? like if exists? i tried that however, it did not work. are there other ways to do this? thank you. here is my code.


DECLARE @searchCommand as VARCHAR(225)
DECLARE searchAccount CURSOR SCROLL FOR
	(SELECT 'SELECT * FROM ' + QUOTENAME(TABLE_NAME) 
	  	+ ' WHERE ' + QUOTENAME(COLUMN_NAME) 
	 	+ '=''4898CC21-2EE6-4E0D-BD31-E8E53B52CF06''' AS COMMANDS
	FROM INFORMATION_SCHEMA.COLUMNS c 
	WHERE DATA_TYPE='uniqueidentifier' 
	AND EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES 
	WHERE TABLE_NAME=c.TABLE_NAME and TABLE_TYPE='BASE TABLE'))

OPEN searchAccount 

	FETCH FIRST FROM searchAccount INTO @searchCommand
	
	WHILE @@FETCH_STATUS = 0
	 BEGIN 
		EXECUTE(@searchCommand)
 		PRINT @searchCommand
	FETCH NEXT FROM searchAccount INTO @searchCommand
	 
	END
CLOSE searchAccount
DEALLOCATE searchAccount

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-02-18 : 08:00:56

I'm not entirely clear what you mean by "if exists", but it sounds like you want to determine which tables & columns contain matching rows. This should do the trick:

declare @sql varchar(max)
set nocount on
create table #m(tbl varchar(128), col varchar(128))
SELECT @sql=isnull(@sql, '') + replace(replace('insert #m select '' ! '', '' ? '' where exists(select * from [ ! ] where [ ? ]=''4898CC21-2EE6-4E0D-BD31-E8E53B52CF06''); ',' ! ', table_name), ' ? ', column_name)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE DATA_TYPE='uniqueidentifier' AND EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=C.TABLE_NAME AND TABLE_TYPE='BASE TABLE')
exec(@sql)
SELECT * FROM #m
drop table #m

Note: I used ' ! ' and ' ? ' as placeholders for table and column names, and I added the spaces because the square brackets created icons

in the forum post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 09:20:00

quote:

Originally posted by j0shua

i have a question. I have applied your code to my code. and it works!! however, is there a way to check if the resulting command execute(@searchCommand) exist? like if exists? i tried that however, it did not work. are there other ways to do this? thank you. here is my code.


DECLARE @searchCommand as VARCHAR(225)
DECLARE searchAccount CURSOR SCROLL FOR
	(SELECT 'SELECT * FROM ' + QUOTENAME(TABLE_NAME) 
	  	+ ' WHERE ' + QUOTENAME(COLUMN_NAME) 
	 	+ '=''4898CC21-2EE6-4E0D-BD31-E8E53B52CF06''' AS COMMANDS
	FROM INFORMATION_SCHEMA.COLUMNS c 
	WHERE DATA_TYPE='uniqueidentifier' 
	AND EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES 
	WHERE TABLE_NAME=c.TABLE_NAME and TABLE_TYPE='BASE TABLE'))

OPEN searchAccount 

	FETCH FIRST FROM searchAccount INTO @searchCommand
	
	WHILE @@FETCH_STATUS = 0 AND LEN(@searchCommand) > 0
	 BEGIN 
		EXECUTE(@searchCommand)
 		PRINT @searchCommand
	FETCH NEXT FROM searchAccount INTO @searchCommand
	 
	END
CLOSE searchAccount
DEALLOCATE searchAccount

isnt it enough to add a condition like this?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-02-18 : 22:29:18

Well, as I said it wasn't clear, but with the actual code posted, the added AND LEN(@searchCommand) > 0 condition would be redundant. The SELECT would never return an empty string unless there were no uniqueidentifier columns in the database. In fact I think it would return null in that instance, but I haven't tested it.

j0shua
Starting Member

40 Posts

Posted - 2009-02-18 : 22:33:10

Thanks for your replies!! :) i really appreciated it. i have just inserted the "if exists" statement inside the variable. it went like this.


		DECLARE @searchCommand as VARCHAR(500)
			DECLARE searchAccount CURSOR SCROLL FOR
				(
				SELECT 'IF EXISTS(SELECT * FROM ' + QUOTENAME(TABLE_NAME) 
				  	+ ' WHERE ' + QUOTENAME(COLUMN_NAME) 
				 	+ '=' + QUOTENAME(@accountID_temp,'''') + ')' +
			
				' SELECT ' + QUOTENAME(TABLE_NAME,'''') + ' AS [TABLE],' 
				+ QUOTENAME(TABLE_NAME) + '.' + QUOTENAME(COLUMN_NAME) 
				+ ', mAccounts.acnt_desc AS [ACCOUNT DESCRIPTION]'
				+ ' FROM ' + QUOTENAME(TABLE_NAME) + ' LEFT JOIN ' + 'mAccounts'
				+ ' ON ' + QUOTENAME(TABLE_NAME) + '.' + QUOTENAME(COLUMN_NAME) + '= mAccounts.acnt_id'
				+ ' WHERE ' + QUOTENAME(TABLE_NAME) + '.' + QUOTENAME(COLUMN_NAME) + '=' + QUOTENAME(@accountID_temp,'''') + '' AS [COMMAND]
				FROM INFORMATION_SCHEMA.COLUMNS c 
				WHERE DATA_TYPE='uniqueidentifier' 
				AND EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES 
				WHERE TABLE_NAME=c.TABLE_NAME and TABLE_TYPE='BASE TABLE')
				)
			
			OPEN searchAccount 
			
				FETCH FIRST FROM searchAccount INTO @searchCommand
				
				WHILE @@FETCH_STATUS = 0
				 BEGIN 
					EXECUTE(@searchCommand)
			 		PRINT @searchCommand

				FETCH NEXT FROM searchAccount INTO @searchCommand
				 
				END
			CLOSE searchAccount
			DEALLOCATE searchAccount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources