CODEWITHSUNDEEP
sqlsql-serverloopsdynamic-sql
Jacob Alley
Jacob Alley

Reputation: 834

How to iterate through tables in a schema with Dynamic Sql

I have a database of tables, and i want to iterate through each table in 3 different schema, one schema at a time.

I figure that i'll need something along the lines of :

DECLARE @tableName varchar(50)

DECLARE @schemaName varchar(50)

For now lets call the schemas "A" "B" and "C".

I can get a list of the tables from each schema using :

SELECT t.name 
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = N'schema_name';

but im not sure how to iterate through that list (id like to insert values in to every table one by one, and they are dependent on the datatype of the columns of the tables so i cant just do a blanket insert into all statement).

Upvotes: 5

Views: 4901

Answers (1)

Matt
Matt

Reputation: 14381

DECLARE @SchemaName SYSNAME = 'dbo'

DECLARE @TableName SYSNAME
--note sysname is the same thing as NVARCHAR()

DECLARE CursorName CURSOR FOR
SELECT t.name 
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = @SchemaName;

OPEN CursorName

FETCH NEXT FROM CursorName
INTO @TableName

    WHILE @@FETCH_STATUS = 0
    BEGIN

       DECLARE @Columns NVARCHAR(MAX)
       SET @Columns = 
       STUFF(
          (SELECT
             ',' + QUOTENAME(name)
          FROM
             sys.columns
          WHERE
             object_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName))
          FOR XML PATH(''))
          ,1,1,'')

        DECLARE @SQL AS NVARCHAR(MAX)
        SET @SQL = 'INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + 
                    ' (' + @Columns + ') VALUES (' + 
                    'YOU MAKE REST DEPENDING ON YOUR NEEDS'

        --use print to view and copy your dynamic sql string to see if you have formed it correctly
        PRINT @SQL
        --EXECUTE (@SQL)

        FETCH NEXT FROM CursorName
        INTO @TableName
    END

CLOSE CursorName
DEALLOCATE CursorName

Upvotes: 7

Related Questions