Force Query to "Use" All Databases In A Server? 1

[kjv1611]

This is not something that has to be, and I don't know whether it's even possible, but just a thought I had.

I've got this simple query I put together for searching SQL databases for certain schemas, tables, and/or fields:

USE DatabaseName [GREEN]--change this line to match whatever database you need to search[/GREEN]
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT OFF

SELECT	 TABLE_CATALOG				AS [Database]
		,TABLE_SCHEMA				AS [Schema]
		,TABLE_NAME					AS [TABLE]
		,COLUMN_NAME				AS [Column]
		,ORDINAL_POSITION			AS [OrdPos]
		,COLUMN_DEFAULT				AS [DefVal]
		,IS_NULLABLE				AS [Nullable]
		,DATA_TYPE					AS [Type]
		,CHARACTER_MAXIMUM_LENGTH	AS [Length]
		,NUMERIC_PRECISION			AS [Precision]
		,NUMERIC_SCALE				AS [Scale]
		,'[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS [CopyToSql]
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	
		[GREEN]--(TABLE_NAME LIKE '%TableName%')	--Comment this line & line below if not specifying table name
		--	AND --Comment this line & line above if not specifying table name
		--(TABLE_SCHEMA LIKE '%SchemaName%') --Comment this line & line below if not specifying schema/user name
		--	AND --Comment this line & line above if not specifying schema/user name[/GREEN]
		(COLUMN_NAME LIKE '%Status%')[GREEN] --Comment  this line if not specifying column name[/GREEN]
ORDER BY TABLE_CATALOG ,TABLE_SCHEMA ,TABLE_NAME ,COLUMN_NAME

So far the code works very well. It gives me what I would actually be looking for practically all of the time, and a little bit of info that I wouldn't necessarily need, but might come in handy at times. That, and it fits totally on the screen, column-wise.

But what I'd like to do is set it up to where it pulls this from very database on any server I run it on. For instance, there are at least 5 or 6 MS SQL servers that I could currently need to query data from at work. If I could set this up to run on the server, regardless of database, and just pull everything, that'd be really helpful at times.

Thanks for any thoughts, references, or suggestions, even if the only answer is: "It's not possible."

 

[gmmastros]

You can use an undocumented procedure named sp_MSForEachDB to accomplish this:

sp_msforeachdb '
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT OFF

If ''?'' Not In (''master'',''model'',''tempdb'')
SELECT     TABLE_CATALOG                AS [Database]
        ,TABLE_SCHEMA                AS [Schema]
        ,TABLE_NAME                    AS [TABLE]
        ,COLUMN_NAME                AS [Column]
        ,ORDINAL_POSITION            AS [OrdPos]
        ,COLUMN_DEFAULT                AS [DefVal]
        ,IS_NULLABLE                AS [Nullable]
        ,DATA_TYPE                    AS [Type]
        ,CHARACTER_MAXIMUM_LENGTH    AS [Length]
        ,NUMERIC_PRECISION            AS [Precision]
        ,NUMERIC_SCALE                AS [Scale]
        ,''['' + TABLE_CATALOG + ''].['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'' AS [CopyToSql]
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE    
        --(TABLE_NAME LIKE ''%TableName%'')    --Comment this line & line below if not specifying table name
        --    AND --Comment this line & line above if not specifying table name
        --(TABLE_SCHEMA LIKE ''%SchemaName%'') --Comment this line & line below if not specifying schema/user name
        --    AND --Comment this line & line above if not specifying schema/user name
        (COLUMN_NAME LIKE ''%Student%'') --Comment  this line if not specifying column name
ORDER BY TABLE_CATALOG ,TABLE_SCHEMA ,TABLE_NAME ,COLUMN_NAME '

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

Google" is your friend. I found this in about 3 seconds of searching on Google:

EXEC sp_databases
EXEC sp_helpdb

 

[kjv1611]

gmmastros,

Thanks. That looks really interesting, and it runs, but when I tested it, it's returning the same results multiple times, rather than results for each database. Is there some setting that needs to be different in the editor, perhaps? I like the idea, so I might tinker with it some to see what I can come up with.

jbenson001,

Yeah, I honestly didn't search this time. However, after I did, I'm not 100% sure of all the info I'm finding.

The discussion here was interesting:

http://social.msdn.microsoft.com/Fo.../thread/480d0032-27d7-4f16-abcb-da72b3045459/

One really short SQL statement looked particularly interesting:

sp_MSforeachdb 'select ''?'',* from [?].INFORMATION_SCHEMA.TABLES'

However, I need to see hwo to expand it... I suppose I can just look into merging it with what gmmastros gave..

I'll keep looking into it..

Thanks for all the suggestions so far..

Oh, and I haven't looked at these 2 yet, but will soon... hopefully..
EXEC sp_databases
EXEC sp_helpdb

 

[kjv1611]

THAT'S IT!!!!

I just needed to add the [?]. piece to tell it to change the database as it loops... at least I guess that's what it's doing.. and it returned just what I was looking for!

So thanks a bunch!

 

[kjv1611]

So final code looked like this:

sp_msforeachdb '
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT OFF

If ''?'' Not In (''master'',''model'',''tempdb'')
SELECT     TABLE_CATALOG                AS [Database]
        ,TABLE_SCHEMA                AS [Schema]
        ,TABLE_NAME                    AS [TABLE]
        ,COLUMN_NAME                AS [Column]
        ,ORDINAL_POSITION            AS [OrdPos]
        ,COLUMN_DEFAULT                AS [DefVal]
        ,IS_NULLABLE                AS [Nullable]
        ,DATA_TYPE                    AS [Type]
        ,CHARACTER_MAXIMUM_LENGTH    AS [Length]
        ,NUMERIC_PRECISION            AS [Precision]
        ,NUMERIC_SCALE                AS [Scale]
        ,''['' + TABLE_CATALOG + ''].['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'' AS [CopyToSql]
FROM    [HIGHLIGHT][?].[/HIGHLIGHT]INFORMATION_SCHEMA.COLUMNS
WHERE    
        --(TABLE_NAME LIKE ''%TableName%'')    --Comment this line & line below if not specifying table name
        --    AND --Comment this line & line above if not specifying table name
        --(TABLE_SCHEMA LIKE ''%SchemaName%'') --Comment this line & line below if not specifying schema/user name
        --    AND --Comment this line & line above if not specifying schema/user name
        (COLUMN_NAME LIKE ''%Status%'') --Comment  this line if not specifying column name
ORDER BY TABLE_CATALOG ,TABLE_SCHEMA ,TABLE_NAME ,COLUMN_NAME'

 

[gmmastros]

You're welcome.

Another thing that may interest you.... you probably noticed that you get a separate result set for each database. You can create a temp table before the sp_msforeachdb call that matches your data types. Change the "Select" code inside the sp_msforeachdb call so that it inserts in to the temp table, and then AFTER the call, select from the temp table. This will effectively give you one result set instead of many.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kjv1611]

Actually, you read my mind! I was wanting to get it in just one result-set, b/c it'd be easier to read, I think.. Yeah, I am purty sure I can handle that part. I will put together the additional bits, and then paste it all here together... the way I'd do it, anyway.. and of course assuming my methods work correctly.

Thanks again

 

[kjv1611]

Alright, here it is updated to put it into one result-set. I just need to get it to where you can just open the sql file and run the whole thing. I suppose I'll need to use the EXEC command to get that to work, or is that even possible with using the sp_ commands? I guess I'll find out soon enough - may not have time to tinker with this anymore for at least the rest of today..

USE Sandbox
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT OFF

IF EXISTS(
	SELECT 1 
	FROM	Sandbox.sys.objects o 
				INNER JOIN 
			Sandbox.sys.schemas s 
				ON o.schema_id = s.schema_id
	WHERE '[' + s.name + '].[' + o.name + ']' = '[' + system_user + '].[InfoSchema]'
	)
BEGIN 
	DROP TABLE [Sandbox]..[InfoSchema]
END

CREATE TABLE [Sandbox]..[InfoSchema](
	 [Database]		varchar(max)		NOT NULL
	,[Schema]		varchar(max)	NOT NULL
	,[Table]		varchar(max)	NOT NULL
	,[Column]		varchar(max)	NOT NULL
	,[OrdPos]		int				NOT NULL
	,[DefVal]		varchar(max)		NULL
	,[Nullable]		varchar(max)		NOT NULL
	,[Type]			varchar(max)		NOT NULL
	,[Length]		int				NULL
	,[Precision]	int				NULL
	,[Scale]		int				NULL
	,[CopyToSql]	varchar(max)	NOT NULL
)

sp_msforeachdb '
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT OFF

If ''?'' Not In (''master'',''model'',''tempdb'')
INSERT INTO Sandbox..InfoSchema
SELECT     TABLE_CATALOG                AS [Database]
        ,TABLE_SCHEMA                AS [Schema]
        ,TABLE_NAME                    AS [TABLE]
        ,COLUMN_NAME                AS [Column]
        ,ORDINAL_POSITION            AS [OrdPos]
        ,COLUMN_DEFAULT                AS [DefVal]
        ,IS_NULLABLE                AS [Nullable]
        ,DATA_TYPE                    AS [Type]
        ,CHARACTER_MAXIMUM_LENGTH    AS [Length]
        ,NUMERIC_PRECISION            AS [Precision]
        ,NUMERIC_SCALE                AS [Scale]
        ,''['' + TABLE_CATALOG + ''].['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'' AS [CopyToSql]
FROM    [?].INFORMATION_SCHEMA.COLUMNS
WHERE    
        --(TABLE_NAME LIKE ''%TableName%'')    --Comment this line & line below if not specifying table name
        --    AND --Comment this line & line above if not specifying table name
        --(TABLE_SCHEMA LIKE ''%SchemaName%'') --Comment this line & line below if not specifying schema/user name
        --    AND --Comment this line & line above if not specifying schema/user name
        (COLUMN_NAME LIKE ''%Status%'') --Comment  this line if not specifying column name
--ORDER BY TABLE_CATALOG ,TABLE_SCHEMA ,TABLE_NAME ,COLUMN_NAME '

SELECT * FROM Sandbox..InfoSchema
ORDER BY [Database] ,[Schema],[Table] ,[Column]

 

[kjv1611]

Follow-up. I tried running the above, and tried shortening the SQL behind the stored procedure, but I'm running into the issue that you apparently cannot run a SQL statement before a stored procedure - or at least it seems so.

Do I have to use the EXEC command? (didn't try that yet)... guess I'll try that next..

Thanks for any ideas.

 

[kjv1611]

Well, the do run separately correctly... so that's fine... I must have been thinking how I wanted to just run the whole file without having to stop for each section, and run them each... it's no big deal to run separately, but I'll take a look at using the EXEC statement I mentioned anyway..