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 |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2004-06-28 : 09:14:44
|
| How to find the list of all the USER CREATED OBJECTS on a DB server?------------------------I think, therefore I am |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-28 : 09:33:33
|
| SELECT name, xtypeFROM sysobjectsWHERE xtype IN ('FN','IF','P','TF','TR','U','V')This will give you all stored procedures, functions, and user tables. To add constraints and foreign keys, you will need to:SELECT name, xtypeFROM sysobjectsWHERE xtype NOT IN ('L','S')MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2004-06-28 : 09:55:57
|
| This will list system SP's how to avoid them?------------------------I think, therefore I am |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-28 : 10:56:04
|
| Remove the 'P' from the WHERE xtype IN ('FN','IF','P','TF','TR','U','V')Kristen |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2004-06-28 : 11:12:32
|
| If i remove 'P' then none of the SP's will be listed. I want the SP's created by user but not system SP's !------------------------I think, therefore I am |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-28 : 12:22:22
|
| Ah, the old Read the Question error :-(Don't know a way to do that. If you're very good about not using "sp_..." for your own SProcs that's a possibility.You might also be able to exclude based on some specific Create Date ranges (which will presumnably need to cover each time you install a service pack)This will only apply to MASTER presumably? Except there is the "dt_..." crud from VIsual Studio etc. that litters all the databases.Kristen |
|
|
|
|
|
|
|
|
List of objects