December 27, 2011 at 8:13 am
I am dealing with a database that has around 2500 tables in it (not my design) and often query Information_schema to find all the tables with a certain column name. I would like to expand that logic further to say "Show me all the tables with a certain column name and where that column contains a certain value".
For example:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'PART_CODE'
[and PART_CODE = 03000]
I appreciate any help anyone can provide on how to do this.
Thank You
December 27, 2011 at 8:38 am
Here is some code I put together a few years ago. This code is horribly slow!!!! There is now way around this being slow. DO NOT RUN THIS ON YOUR PRODUCTION SERVER. You can run this on a dev server but don't expect immediate results. Start running it and go get a cup of coffee, lunch and then take a nap. 😉
declare @table_name varchar(2000)
declare @sSQL nvarchar(4000)
declare @result varchar(20)
declare @column_name varchar(2000)
declare @SearchVal varchar(200)
set @SearchVal = '%your search val here%'
declare @ColName varchar (250)
set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'
declare SearchList cursor for
select distinct so.name,sc.name from syscolumns sc
inner join sysobjects so on sc.id = so.id
where sc.name like @ColName
and so.type = 'U'
open SearchList
fetch next from SearchList into @table_name, @column_name
while(@@fetch_status = 0)
begin
select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''
exec sp_executesql @sSQL
--select @ssql
fetch next from SearchList into @table_name, @column_name
end
close SearchList
deallocate SearchList
Did I mention DO NOT RUN THIS IN PRODUCTION???????
This type of searching just simply is not fast.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 27, 2011 at 8:44 am
Nice one Sean, I was going to suggest Power shell to do this stuff. First get the Table name from Information Schema and then use a while loop and execute the column condition.
-Roy
December 27, 2011 at 8:49 am
Thanks Roy. I just hope the OP realizes how important it is to not run this in production. The first time I ran this on a production box it took close to 4 hours. Fortunately it was after hours and it was a system that didn't get any after hours traffic. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 27, 2011 at 8:55 am
Sean Lange (12/27/2011)
Thanks Roy. I just hope the OP realizes how important it is to not run this in production. The first time I ran this on a production box it took close to 4 hours. Fortunately it was after hours and it was a system that didn't get any after hours traffic. 😛
I am not an expert on T-SQL (on any criteria) but I noticed a cursor in your code. I am surprised to see it because you are one of the valuable resources on SSC who criticize on use of cursors and suggest many options to get rid of it. Is it not possible here?
December 27, 2011 at 9:05 am
Dev (12/27/2011)
Sean Lange (12/27/2011)
Thanks Roy. I just hope the OP realizes how important it is to not run this in production. The first time I ran this on a production box it took close to 4 hours. Fortunately it was after hours and it was a system that didn't get any after hours traffic. 😛I am not an expert on T-SQL (on any criteria) but I noticed a cursor in your code. I am surprised to see it because you are one of the valuable resources on SSC who criticize on use of cursors and suggest many options to get rid of it. Is it not possible here?
Thanks for compliment. :blush:
I don't know of any around a cursor for this type of thing. There are a few situations where a cursor really is about the way and this is one of them. This code is looking through all tables that have a column with a specific name and then selects the row(s) if any exist.
I know several others around here who are as opposed to cursors as I am have written somewhat similar search scripts in the past. Lowell has one that is more robust than mine in a number of areas that very close to what I did and I am sure there are others. This type of script is a dev/data discovery type of tool. It is by no means intended to be used in anything resembling a daily script.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 27, 2011 at 9:11 am
Sean Lange (12/27/2011)
Dev (12/27/2011)
Sean Lange (12/27/2011)
Thanks Roy. I just hope the OP realizes how important it is to not run this in production. The first time I ran this on a production box it took close to 4 hours. Fortunately it was after hours and it was a system that didn't get any after hours traffic. 😛I am not an expert on T-SQL (on any criteria) but I noticed a cursor in your code. I am surprised to see it because you are one of the valuable resources on SSC who criticize on use of cursors and suggest many options to get rid of it. Is it not possible here?
Thanks for compliment. :blush:
I don't know of any around a cursor for this type of thing. There are a few situations where a cursor really is about the way and this is one of them. This code is looking through all tables that have a column with a specific name and then selects the row(s) if any exist.
I know several others around here who are as opposed to cursors as I am have written somewhat similar search scripts in the past. Lowell has one that is more robust than mine in a number of areas that very close to what I did and I am sure there are others. This type of script is a dev/data discovery type of tool. It is by no means intended to be used in anything resembling a daily script.
This is what I thought. Nice Job indeed. 🙂
December 27, 2011 at 9:22 am
The only advantage of using PS instead of T-SQL would be that the Cursor or While loop that has to be used is not on the DB layer but on the OS level. 🙂
-Roy
December 27, 2011 at 9:26 am
The only disadvantage with power shell is it’s a new language in its own. It’s new learning curve. 😀
December 27, 2011 at 10:03 am
Thank you for the response and the code...I ran it on our test server and it seems to work great! It only takes a few seconds to complete and seems to show all the tables with the specified column and value in the column so I dont see the speed problem??
Thank you...this will save me a ton of hunt-and-peck time
December 27, 2011 at 10:07 am
stourault (12/27/2011)
Thank you for the response and the code...I ran it on our test server and it seems to work great! It only takes a few seconds to complete and seems to show all the tables with the specified column and value in the column so I dont see the speed problem??Thank you...this will save me a ton of hunt-and-peck time
It likely is not too bad since you are limiting to pretty specific column names. If you have a large of columns the speed will go the way of dodo bird. Glad that is working for you and thanks for letting us know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply