Loop Through every table in a DB

,

this Script is meant to be used to create your own script and by itself it does very little except list all the tables. the parameter @SQLScript can be used to execute some code as you see fit.

 

the idea behind this script is to use it as a template to build your own code.

 

this is based on the original sp called _SPLOG_SPConstructor found in the autogenerate stored procedures code which can be found here:

 

http://www.sqlservercentral.com/scripts/AutoGenerate/65042/

 

and here:

 

http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/

 

Enjoy...

 

 

 

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

--PART OF THE MACRO CODE EXECUTION BY CHRIS MORTON

--CONTACT CBMORTON@GMAIL.COM

--2009-01-05
-- =============================================

ALTER PROCEDURE [dbo].[x_TableLoops]

 @SQLScript NVARCHAR(MAX) = NULL

AS 

 BEGIN


 DECLARE @tables TABLE

 (

 id BIGINT IDENTITY(1, 1),

 [table] VARCHAR(50)

 )

    

 INSERT INTO @tables ( [table] )

 SELECT TABLE_NAME AS [table]

 FROM INFORMATION_SCHEMA.tables

 WHERE TABLE_TYPE = 'BASE TABLE'

 AND TABLE_NAME <> 'sysdiagrams'

 
 DECLARE @tablename VARCHAR(50)

 DECLARE @Counter BIGINT 

 SET @counter = 1

 WHILE @counter <= ( SELECT MAX(id)

 FROM @tables

 )

 BEGIN

 SET @tablename = ( SELECT [TABLE]

 FROM @tables

 WHERE id = @counter

 ) 

 IF @SQLsCRIPT IS NOT NULL 

 BEGIN

 EXEC ( @sqlscript

 )

 END

 ELSE 

 BEGIN

 PRINT @tablename

 END

 

 SET @Counter = @counter + 1

    

 END

    

 

    

 END

Rate

3.33 (6)

Share

Share

Rate

3.33 (6)