October 29, 2019 at 3:31 pm
I have a database named "MyDB".
The DB has hundreds of tables, views, functions, triggers, stored procedures, etc....
How can I extract a listing of all the Objects in the DB along with the object type for each?
Thanks,
John
Remember... If you can't control it then don't sweat it!
October 29, 2019 at 6:02 pm
A nice easy question once you know where to look. Have a look at the table sys.objects for that.
The [name] column shows you the name of the object, the [type_desc] column will tell you the object type including triggers, stored procedures, user tables, views, primary keys, foreign keys, etc.
It also contains the created_date and modify_date and has a column "is_ms_shipped" to indicate if Microsoft put that one there.
I recommend doing a SELECT TOP 100 * on it at first so you can build the query up the way that makes sense for your need then trim out the fluff. For example, when I look at it, I don't care about the object_id, principal_id, schema_id, type (just the type_desc as it is more "friendly" to read), is_published or is_schema_publised. parent_object_id is useful for mapping a trigger or a key to a table. But it depends on what you are looking at/for.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 29, 2019 at 6:04 pm
If all you need is just listing all the objects and corresponding type in your DB, you could query sys.objects. something like...."select name,type_desc from sys.objects" within your database.
November 4, 2019 at 2:51 pm
Hey John,
Based on the previous answers, the sys.objects view with a search condition on is_ms_shipped would likely be your best bet to accomplish this.
Using SQL Server 2017, you can also use the CONCAT_WS function to concatenate string characters with a specified delimiter.
Please try the following (quotes can be removed if not preferred):
SELECT CONCAT_WS(N',', QUOTENAME(name COLLATE DATABASE_DEFAULT, '"'), QUOTENAME(type, '"'), QUOTENAME(type_desc, '"')) AS DataBaseObjects
FROM sys.objects
WHERE is_ms_shipped = 0;
November 4, 2019 at 4:12 pm
I agree that sys.objects is a good source for the brunt of what you're looking for. Unfortunately, it doesn't cover everything. For example, indexes, statistics, and partitions aren't covered by sys.objects.
I don't remember, off the top of my head, everything that isn't covered by sys.objects but wanted to let you know that it may not be the panacea you seek.
As a bit of a sidebar, I'm pissed at Microsoft for that short coming and the related shortcoming of not being able to query for (for example) ALL tables (etc) in an entire instance without having to use something like sp_MSForEachDB or some other custom code.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply