How to use a while loop to iterate through each table of each database within an instance

Say you have code you want executed against every table on a SQL Server instance, you could use SQL Server’s inbuilt sp_MSForEachDB and sp_MSForEachTable. I’m not a big fan of them though because they are undocumented, so I’d always be concerned Microsoft might decide to kill it with any given patch or service pack update. (I know the likelihood of that is extremely low but I’m a risk adverse kinda guy)

I prefer to use the example below. It may not be the most efficient snippet of code available on the net but it’s good and simple and it’s not going anywhere unless I drop it. 

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Table') IS NOT NULL
	DROP TABLE #Table;

DECLARE @Database TABLE (DbName SYSNAME)
DECLARE @DbName AS SYSNAME
DECLARE @TableName AS SYSNAME
DECLARE @SchemaName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)
DECLARE @s AS INT
DECLARE @f AS INT

CREATE TABLE #Table (
	Id INT IDENTITY(1, 1)
	,DbName SYSNAME
	,SchemaName SYSNAME
	,TableName SYSNAME
	);

SET @DbName = ''

INSERT INTO @Database (DbName)
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC

WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			)
	SET @Sql = '
INSERT INTO #Table (
	DbName
	,SchemaName
	,TableName
	)
SELECT ' + '''' + @DbName + '''' + '
	,s.NAME
	,so.NAME
FROM ' + QUOTENAME(@DbName) + '.sys.tables AS so
LEFT JOIN ' + QUOTENAME(@DbName) + '.sys.schemas AS s ON so.schema_id = s.schema_id
ORDER BY s.NAME ASC'

	EXEC (@Sql)
END

SET @s = (
		SELECT MIN(Id)
		FROM #Table
		)
SET @f = (
		SELECT MAX(Id)
		FROM #Table
		) + 1

WHILE @s < @f
BEGIN

	/*
	PUT CODE HERE
	EXAMPLE PRINT Id
	*/
	PRINT @s

	SET @s = @s + 1
END
Advertisements

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