Convert SQL script to work on parallel data warehouse

• mahajan.344

  SSC Journeyman

  Points: 76

  More actions

  July 23, 2015 at 8:29 pm

  #319956

  Hi,

  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.

  HERE is SQL Script

  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

  ,

  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