Code Correction

Question

Post reply

Code Correction

mtz676

SSCrazy Eights

Points: 9214

April 18, 2023 at 7:26 am

#4175544

The underneath code works on higher sql versions(2016 etc) but throws an error on 2008R2.

Error:Msg 102, Level 15, State 1, Line 19

Incorrect syntax near '('.

Thanks

SET NOCOUNT ON
declare @tmp table(name nvarchar(500),Status int)
insert into @tmp SELECT name,0 as completed
FROM sys.databases
WHERE (state_desc = 'ONLINE')
DECLARE @dbname nvarchar(500)
DECLARE @cmd NVARCHAR(MAX)
declare @result table (ServerName nvarchar(500),dbname nvarchar(500),
SchemaName nvarchar(500),TableName nvarchar(500),ObjectType varchar(100),
IndexName nvarchar(500),Index_Type varchar(100),IsIndexUnique varchar(100),Alloc_Unit_Type_Desc nvarchar(500),
avg_fragmentation_in_percent decimal(38,10),Fill_factor int,page_count bigint)
WHILE EXISTS(SELECT 1 FROM @tmp WHERE Status = 0)
BEGIN
SET @dbname = (SELECT TOP 1 name FROM @tmp WHERE Status = 0)
SET @cmd = 'use ['+ @dbname + '];SELECT @@servername as ServerName,db_name(),dbschemas.[name],
dbtables.[name],
case when t.type= ''U'' then ''Table''
     when t.type= ''V'' then ''View''
 else ''Unknown''
 end as 
 [ObjectType],
dbindexes.[name],
dbindexes.type_desc,
case when dbindexes.is_unique = 0 then ''Is Unique''
     when dbindexes.is_unique = 1 then ''Not Unique''
 else ''Unknown''
 end as
 [IsUnique],
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent as Ifg,
dbindexes.Fill_Factor,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
INNER JOIN sys.objects t on t.object_id = dbindexes.object_id
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc'
insert into @result exec sp_executesql @cmd
UPDATE @tmp SET Status = 1 WHERE name = @dbname
END
insert into [dbM].[dbo].[Ifg]
  (
  [ServerName]
      ,[DBName]
      ,[SchemaName]
      ,[TableName]
      ,[ObjectType]
      ,[IndexName]
      ,[Index_Type]
      ,[IsIndexUnique]
      ,[Alloc_Unit_Type_Desc]
      ,[Avg_Fragmentation_In_Percent]
      ,[Fill_Factor]
      ,[Page_Count]
      ,[TSMb]
  )

select *,(page_count*8/1024) as TSMb from @result 
where dbname not in('master','msdb','model','temp','ReportServer','ReportServerTempDB')
AND avg_fragmentation_in_percent > 20 AND page_count > 2000

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16158 More actions · Answer 1

dup post - see reply on https://www.sqlservercentral.com/forums/topic/code-correction-4#post-4175569