Cybercrux

Everything is achievable through technology

Iterate All Schema

USE DBNAME

SELECT * INTO #mytemp FROM INFORMATION_SCHEMA.SCHEMATA
WHERE [SCHEMA_NAME] NOT IN (
'dbo',
'INFORMATION_SCHEMA',
'guest',
'sys',
'db_denydatawriter',
'db_denydatareader',
'db_datawriter',
'db_datareader',
'db_backupoperator',
'db_ddladmin',
'db_securityadmin',
'db_owner',
'db_accessadmin')

WHILE (SELECT Count(*) FROM #mytemp) > 0
BEGIN
DECLARE @SCHEMA_NAME varchar(100)
SELECT @SCHEMA_NAME = [SCHEMA_NAME] FROM #mytemp
DECLARE @SQL VARCHAR(MAX)

-- select each Tenant Name
SELECT [SCHEMA_NAME] AS [Tenant Name] FROM #mytemp WHERE [SCHEMA_NAME] = @SCHEMA_NAME

--Custom Query Begin
SET @SQL='SELECT * FROM [$schemaname].tablename'
SET @SQL=REPLACE(@SQL,'$schemaname',@SCHEMA_NAME);
EXEC (@SQL)
--Custom Query End

DELETE #mytemp WHERE [SCHEMA_NAME] = @SCHEMA_NAME
END
DROP TABLE #mytemp

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s