Tuesday 22 July 2008

Loop All User Tables

Useful for systems functions & reporting -

-- loop tables
SET NOCOUNT ON
DECLARE  @CurrentRow INT
DECLARE  @TotalRows INT

DECLARE  @TableName NVARCHAR(255)   
DECLARE  @SchemaName NVARCHAR(255)   
                
SELECT @CurrentRow = 1
               
DECLARE  @Tables  
TABLE(uniquerowid  INT   IDENTITY ( 1,1 )   PRIMARY KEY   NOT NULL
   ,schemaname NVARCHAR(255)
   ,tablename NVARCHAR(255))
   
INSERT INTO @Tables (schemaname,tablename)
SELECT sys.schemas.name,sys.tables.name
FROM sys.tables
inner join sys.schemas
on sys.tables.schema_id = sys.schemas.schema_id
order by 1,2

               
SELECT @TotalRows = COUNT(* )
FROM @Tables
 
WHILE @CurrentRow <= @TotalRows  
BEGIN    
 SELECT @schemaname = schemaname, @tableName = tablename      
 FROM @Tables     
 WHERE UNIQUEROWID = @CurrentRow         
 PRINT @schemaname + '.' + @tablename         
 SELECT @CurrentRow = @CurrentRow + 1  
END

No comments: