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.
| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-05-13 : 16:51:39
|
| Hi, Since all (user) tables start with 'P' (due to PeopleSoft), I was picking up only indexes starting with 'P%' for index maintenance. I found an index starting with 'tPS....' whose indid = 255 (text/image). Do all names with indid = 255 start with 't%'?Thanks,Sarat. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-13 : 17:01:39
|
I was going to say why don't you use the INFORMATION_SCHEMA view for Indexes...but guess what! There isn't one....and why not?The MS engineers couldn't figure it out?As far as:quote:
Do all names with indid = 255 start with 't%'?
That's a design issue. My guess would be that 255 is for User defined indexes...just a gues though.Can't you just script the db to see all of the indexes you'll have to manage? Didn't PS come with an Admin manual?Lot of tables in PS...Brett8-) |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-05-13 : 17:27:29
|
| well, per bol indid = 255 is for text/image type columns. there isn't any admin manual from PS but they provide scripts depending on db platform and the script is similar to what I wrote specifying sysobjects.name = 'PS%'! there are about 8000 tables in PS. When DBCC DBREINDEX runs, does it take all indexes and also the ones which start with '_WS_sys_....'? If not I need to keep the condition to pick up indexes starting with 'PS%' only in my script but then I will miss the ones with indid = 255 which is probably a question for PSoft usersgroup.I am not what you mean by scripting a db. Are you saying Create Index script for all tables in the database? PS application has a functionality which lets you build an index script for all tables but it is very time consuming and big. I think now though that this may be the way to go.Thanks,sarat.Thanks,Sarat. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-13 : 18:43:00
|
| You can script your database in Enterprise Manager by right clicking in various locations (for instance, right click on your database) in Enterprise Manager, then in one of the menus you'll find generate sql script. That's an easy way to script out your database. It has lots of different options to turn on and off also. Other people might recommend scripting out your database in a different way, but this is an easy way of doing it.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-14 : 10:03:17
|
Oh SQL Warrior Princess...please enlighten this peasant... quote:
Other people might recommend scripting out your database in a different way
How?I guess another solution would be to do: DBCC SHOWCONTIG This will show you all of the indexes in your database and how healthy they areMOOBrett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-14 : 12:46:00
|
| Well some people use 3rd party tools to script out their objects. I believe DB Artisan is one of those products. Other people have written scripts to do it.Tara |
|
|
|
|
|
|
Index name starting with 't'