Convert SQL script to work on parallel data warehouse

Question

I have SQL script which when executed runs on all database, Get list of all Tables with record count and Index created on that Table.

However when I try to run that on parallel data warehouse server , Its giving me error. Because parallel data warehouse doesn't allow you to create temporary table variable,variables etc.

I have found alternative for table variable but didn't find anything about declaring varchar variables and then major concern is the way script is using database as variable in order to run the script on all database.I didn't find anything alternative of this.

declare @TableList TABLE(Id int IDENTITY(1,1),DataBaseName VARCHAR(100),TableName VARCHAR(100),RecordCount INT,NameOfIndex VARCHAR(100),TypeOfIndex VARCHAR(100))
declare @TableListWithIndex TABLE(Id int IDENTITY(1,1),DataBaseName VARCHAR(100),TableName VARCHAR(100),NameOfIndex VARCHAR(100),TypeOfIndex VARCHAR(100))
declare @DatabaseNames TABLE(Id INT IDENTITY(1,1),NameOfDataBase VARCHAR(100),DataBaseSize VARCHAR(100),Remarks VARCHAR(500))
declare @sql varchar(1000)
INSERT INTO @DatabaseNames 
EXEC sp_databases



DECLARE @Count INT = 0,@Counter INT = 1 ,@NameOfDb VARCHAR(100);

SELECT @Count = COUNT(1) FROM @DatabaseNames 


WHILE(@Count > 0)
BEGIN
    SELECT @NameOfDb = NameOfDataBase FROM @DatabaseNames WHERE Id = @Counter

    SET @sql = N'SELECT '''+ @NameOfDb +''',T.name AS [TABLE NAME], 
           I.rows AS [ROWCOUNT] 
    FROM   @NameOfDb.sys.tables AS T 
           INNER JOIN @NameOfDb.sys.sysindexes AS I 
                   ON T.object_id = I.id 
                      AND I.indid < 2 
    ORDER  BY I.rows DESC';

    SET @sql    =   REPLACE(@sql, '@NameOfDb', @NameOfDb)
    INSERT INTO @TableList(DataBaseName,TableName,RecordCount)
    exec(@sql);




    SET @sql = N'SELECT 
         '''+ @NameOfDb +''',
         TableName = t.name,
         IndexName = ind.name,
         ind.type_desc
    FROM 
         @NameOfDb.sys.indexes ind 
    INNER JOIN 
         @NameOfDb.sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
    INNER JOIN 
         @NameOfDb.sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
    INNER JOIN 
         @NameOfDb.sys.tables t ON ind.object_id = t.object_id 
    WHERE 
         ind.is_primary_key = 0 
         AND ind.is_unique = 0 
         AND ind.is_unique_constraint = 0 
         AND t.is_ms_shipped = 0 
    ORDER BY 
         t.name, ind.name, ind.index_id, ic.index_column_id ';

    SET @sql    =   REPLACE(@sql, '@NameOfDb', @NameOfDb)
    INSERT INTO @TableListWithIndex(DataBaseName,TableName,NameOfIndex,TypeOfIndex)
    exec(@sql);




    SET @Count = @Count - 1;
    SET @Counter = @Counter + 1;
END

    update TL
     SET TL.NameOfIndex = TLW.NameOfIndex,TL.TypeOfIndex = TLW.TypeOfIndex
     from @TableList TL
     INNER JOIN @TableListWithIndex TLW ON TL.TableName = TLW.TableName 
     AND TL.DataBaseName = TLW.DataBaseName

     select * from @TableList 
     Where DataBaseName NOT IN ('tempdb','ReportServer$MSSQLSERVER2014TempDB','ReportServer$MSSQLSERVER2014','msdb','master','model') 
     order by DataBaseName

Now How can I make this work in parallel data warehouse

score 0 · Answer 1 · answered Jul 24 '15 at 08:53

0

You can use undocumented sp_MSforeachdb procedure to execute script on each database.

answered Jul 24 '15 at 08:53

Dmitry

512
7
15

Convert SQL script to work on parallel data warehouse

1 Answers1