Execute A Simple Query on All Databases

The following executes a simple search for a table against each database on a given database server. I am limited by the fact that sp_Msforeachdb has a 128 character limit to what SQL it can execute.


/*
The following executes a simple search for a table against each database on a server.
Note that sp_Msforeachdb has a 128 character limit to what it can execute.
Complex query requirements will have to be run using dynamic SQL.
*/
USE [tempdb];
GO
--create a results table outside accessible in the context of sp_Msforeachdb
--but not taking up any of the 128 character limit in the sproc signature

CREATE TABLE [dbo].[t1](TABLE_CATALOG
)nVarchar(max), TABLE_SCHEMA nVarchar(max), TABLE_NAME nVarchar(max), COLUMN_NAME nVarchar(max), ORDINAL_POSITION nVarchar(max), COLUMN_DEFAULT nVarchar(max), IS_NULLABLE nVarchar(max), DATA_TYPE nVarchar(max), CHARACTER_MAXIMUM_LENGTH nVarchar(max), CHARACTER_OCTET_LENGTH nVarchar(max), NUMERIC_PRECISION nVarchar(max), NUMERIC_PRECISION_RADIX nVarchar(max), NUMERIC_SCALE nVarchar(max), DATETIME_PRECISION nVarchar(max), CHARACTER_SET_CATALOG nVarchar(max), CHARACTER_SET_SCHEMA nVarchar(max), CHARACTER_SET_NAME nVarchar(max), COLLATION_CATALOG nVarchar(max), COLLATION_SCHEMA nVarchar(max), COLLATION_NAME nVarchar(max), DOMAIN_CATALOG nVarchar(max), DOMAIN_SCHEMA nVarchar(max), DOMAIN_NAME nVarchar(max) ON [PRIMARY]GO

--run your query looking for a table or column or schema
--you only get 128 characters here

EXEC sp_Msforeachdb
"
USE [?] ;
INSERT INTO tempdb.dbo.t1
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'borrower'
"
GO

--filter out empty records with temp table
SELECT * FROM [dbo].[t1] WHERE table_name IS NOT NULL

--clean up temp table
USE tempdb;
DROP TABLE [dbo].[t1];

GO