August 20, 2008 at 9:19 am
Can someone please help me with this query. I'm trying to get the name of every table and the fillfactor. But it is listing the fillfactor for every index.
I keep receiving the error:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DISTINCT'.
SELECT 'DBCC DBREINDEX(' +
DISTINCT (o.name + ',') + -- table name
''' ''' + ',' +
CONVERT(varchar(20),i.fill_factor) + ')' --fill factor
from sys.indexes i
inner join sys.objects o on o.object_id = i.object_id
where o.type <> 'S'
order by o.name
August 20, 2008 at 9:28 am
DISTINCT is a SELECT keyword, not a function.
Try this:
Select cmd as [-- Commands:] From (
SELECT DISTINCT 'DBCC DBREINDEX(' +
(o.name + ',') + -- table name
''' ''' + ',' +
CONVERT(varchar(20),i.fill_factor) + ')' --fill factor
AS [cmd]
, o.name
from sys.indexes i
inner join sys.objects o on o.object_id = i.object_id
where o.type <> 'S') T
order by T.name
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 20, 2008 at 9:31 am
you're getting the error because you can't use DISTINCT on one part of a concatenated column. you'd have to use DISTINCT on the whole column:
SELECT DISTINCT 'DBCC DBREINDEX(' + (o.name + ',') + ''' ''' + ',' + CONVERT(varchar(20),i.fill_factor) + ')' --fill factor
from sys.indexes i
inner join sys.objects o on o.object_id = i.object_id
where o.type <> 'S'
while this will run, you'll still get fill factors per index because fill factors are tied to the index, not the table.
August 20, 2008 at 9:34 am
That is because tables do not have fill factors, only indexes and each index can have a different fill factor.
If you want to rebuild all indexes on a table with the original fill factor, then only provide the table name and the schema name to the DBCC DBREINDEX command.
SELECT 'DBCC DBREINDEX("' +
QUOTENAME ( sys.database_principals.name ) + '.' +
QUOTENAME (sys.objects.name )
+ '") WITH NO_INFOMSGS '
FROM sys.database_principals
join sys.objects
on sys.objects.principal_id = sys.database_principals.principal_id
WHERE sys.objects.type = 'U' -- only user tables
AND EXISTS
(SELECT 1
from sys.indexes
WHERE sys.indexes.object_id = sys.objects.object_id
AND sys.indexes.type in (1,2) -- a rebuildable index
)
SQL = Scarcely Qualifies as a Language
August 20, 2008 at 9:36 am
Try this:
SELECT 'ALTER INDEX ALL ON '+o.name +' REBUILD WITH (FILLFACTOR = '+ CONVERT(varchar(20),i.fill_factor) + ', SORT_IN_TEMPDB = ON )'
from sys.indexes i
inner join sys.objects o on o.object_id = i.object_id
where o.type <> 'S' and i.type = 1 or i.type = 0
order by o.name
August 20, 2008 at 9:59 am
Thank you. I went with moving the distinct to outside of the dbcc reindex. Yes, I needed to rebuild the indexes but we different fillfactors for each table (some are lookups and will NEVER be updated). THank you for your help!
August 20, 2008 at 10:21 am
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply