i NEED A BASIC SCRIPT TO TRAVERSE THROUGH EACH DATABASE

Question

Post reply

i NEED A BASIC SCRIPT TO TRAVERSE THROUGH EACH DATABASE

mw_sql_developer

SSCoach

Points: 19445
More actions
January 19, 2015 at 1:08 pm

#296789

I AM AWARE OF THE of the following
Select name from sys.databases -- will give a list of all databases
I also know how to get the tablename.column_name for each table.
CAn someone show me how I could i9terate through each database ( some king of a loop I am expecting ).
The final objective here is for me to search each database for tables that have the a column by the name "PAtient_Something"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

There is no need to use caps. It's pretty annoying.

Something real quick I came up with:

EXEC master.dbo.sp_msforeachdb 'USE [?];

SELECT t.name, c.name

FROM sys.tablest

JOIN sys.columnsc ON t.[object_id] = c.[object_id]

WHEREt.[type] = ''U''

AND c.name LIKE ''PAtient_Something%'';';

sp_msforeachdb is undocumented. You could replace it with a WHILE loop if you'd like.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

I ran the script, OK!

But the problem is I need the database name listed as well.

That way I will know what database to look for straight away

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Small modification:

EXEC master.dbo.sp_msforeachdb 'USE [?];

SELECT t.name, c.name, ''?'' AS [database]

FROM sys.tablest

JOIN sys.columnsc ON t.[object_id] = c.[object_id]

WHEREt.[type] = ''U''

AND c.name LIKE ''PAtient_Something%'';';

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Note that there are some problems registered when using sp_MSForEachDB. Check the following article for an alternative: http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/

You could also build your own solution using a cursor and dynamic code.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Luis Cazares (1/19/2015)
Note that there are some problems registered when using sp_MSForEachDB. Check the following article for an alternative: http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
You could also build your own solution using a cursor and dynamic code.

Yeah, sp_msforeachdb is not an ideal solution, but I didn't really have the time to whip out a decent script.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP