Tara's isp_DBCC_DBREINDEX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Tara's isp_DBCC_DBREINDEX

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-07-20 : 12:15:29

Hi all,

Tara's sproc is brilliant. However my developers are not :)They created all the user tables with a owner of GISUSER okay so I changed the proc no big deal except for one little problem: there is a table called dbo.dtproperties which totally screws me up. How can I say here okay reindex everything but this one:

WHILE @RowCnt <> 0
BEGIN

 SET @SQL = 'DBCC DBREINDEX(''' + @dbName + '.GISUSER.' + @objName + ''', ' + @idxName + ', 0) WITH NO_INFOMSGS'

 EXEC sp_executesql @statement = @SQL

 SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
 FROM ##Indexes
 WHERE Indexes_ID > @ID
 ORDER BY Indexes_ID
 
 SET @RowCnt = @@ROWCOUNT

END

Thanks for any help.

Laura

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-20 : 13:12:52

dtproperties is a system table, her script should be skipping it anyway. Just glancing at the code it looks like it does (WHERE o.type = 'U').

How have you changed the script?

-ec

TimS
Posting Yak Master

198 Posts

Posted - 2005-07-20 : 13:15:20

FYI: dtproperties is a system table that is reported as a user table by most tests.

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-07-20 : 13:17:35

I had to change

DECLARE @objName SYSNAME
DECLARE @idxName SYSNAME

DECLARE @objName NVARCHAR(128)
DECLARE @idxName NVARCHAR(128)

since everytime I ran it I kept getting the message "Column or parameter #1: Cannot find data type SYSNAME." What's odd is it works just as advertised on every other server I have.

Kristen
Test

22859 Posts

Posted - 2005-07-20 : 13:18:49

[code]
WHERE Indexes_ID > @ID
AND @objName <> 'dtproperties'
[/code]
???

I think it would be better if you modified the procedure so that it actually worked out what the Owner was, rather than just forcing it to be "GISUSER", but I guess that's up to you.

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-20 : 13:29:32

quote:
Originally posted by TimS

FYI: dtproperties is a system table that is reported as a user table by most tests.

you are right. In sysobjects it's type is 'U'.

-ec

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-07-20 : 13:34:05

Kristen,

I would like to do that and I saw on Tara's blog that someone said they did and told how 'sort of' at least not in a way I could make work:

" # re: DBCC DBREINDEX Stored Procedure - new version #2
Cord thomas
Tara - good work.

One issue i note with this procedure is that it assumes all the tables/databases are using the default dbo schema - you need to get the sysuser from the sysobject uid and add to the list of fields in ##Indexes - otherwise, this looks great.

Add to ##Indexes

ownername SYSNAME

Add to processing of each one
u.name in the list of fields to insert and

join ' + @dbName + '.dbo.sysusers u on o.uid = u.uid '

to the query specs
Posted @ 1/18/2005 4:46 PM "

Kristen
Test

22859 Posts

Posted - 2005-07-20 : 14:34:29

sysname is functionally equivalent to nvarchar(128) so you should be OK with the deinfitions you used.

Is there any possibility that your database is case sensitive? If so I suppose its possible that you might need to use "sysname" in lowercase

Kristen

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-07-20 : 14:39:37

Nice catch :) Why yes it is. Its the only server that does this, and I keep forgetting. I've almost got the owner name piece added although it keeps saying: Cannot resolve collation conflict for equal to operation. It also has something to do with the case sensitivity deosn't it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 15:07:10

I do not see that my script is including dtproperties. Here is the most recent copy of the stored procedure:
http://weblogs.sqlteam.com/tarad/archive/2005/01/04/3933.aspx

Which INSERT INTO ##Indexes is capturing dtproperties?

Tara

Kristen
Test

22859 Posts

Posted - 2005-07-21 : 01:54:10

"Cannot resolve collation conflict for equal to operation"

Force a collation of your choice, e.g.
...
join ' + @dbName + '.dbo.sysusers u on o.uid COLLATE SQL_Latin1_General_CP1_CI_AS = u.uid '
...
Kristen

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-07-21 : 07:02:34

Hi Tara,

It's the first insert (I've been messing with the code trying to get it to work for this database):


-- non-unique clustered indexes
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName, Status, Ownername) '
SET @SQL = @SQL + 'SELECT i.[name], o.[name], i.status, u.[name]'
SET @SQL = @SQL + 'FROM GIS.dbo.sysindexes i '
SET @SQL = @SQL + 'INNER JOIN GIS.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.id = o.id '
SET @SQL = @SQL + 'join GIS.dbo.sysusers u on o.uid = u.uid ' 
SET @SQL = @SQL + 'WHERE i.indid = 1 AND '
SET @SQL = @SQL + 'o.name <> ''dtproperties'' '
SET @SQL = @SQL + 'o.type = ''U'''

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-21 : 12:11:48

You are missing part of the WHERE clause. Here it is:

SET @SQL = @SQL + 'WHERE i.indid = 1 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND (i.status & 2) = 0'

Tara

TimS
Posting Yak Master

198 Posts

Posted - 2005-07-21 : 19:54:12

FYI:

I just posted a version using SchemaName on http://weblogs.sqlteam.com/tarad/archive/2005/01/04/3933.aspx?Pending=true

Edit: It needs testing, but it seemed to work OK to me.

Tim S

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-22 : 01:54:15

Thanks Tim!

Tara

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-07-25 : 08:11:27

Thanks to all.

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources