Listing user created objects

Question

Listing user created objects

tnpich

Ten Centuries

Points: 1223
More actions
October 7, 2015 at 7:47 am

#301331

I'm trying to list everything (tables, view, procedures, functions, etc.) that was created by users in a database.
The query which seems to eliminate the most SQL system type objects is shown below.
SELECT *
FROM sys.all_objects SAO
WHERE SAO.is_ms_shipped = 0
order by SAO.type, SAO.name
This still includes some non-user created objects, like the below. See the attachment for details.
fn_diagramobjects
sp_alterdiagram
sp_creatediagram
How can I get rid of these type of objects without filtering on SAO.name LIKE...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

tnpich (10/7/2015)
I'm trying to list everything (tables, view, procedures, functions, etc.) that was created by users in a database.
The query which seems to eliminate the most SQL system type objects is shown below.
SELECT *
FROM sys.all_objects SAO
WHERE SAO.is_ms_shipped = 0
order by SAO.type, SAO.name
This still includes some non-user created objects, like the below. See the attachment for details.
fn_diagramobjects
sp_alterdiagram
sp_creatediagram
How can I get rid of these type of objects without filtering on SAO.name LIKE...

I don't believe you can just based on the data in the table. You'll need to either do a NOT LIKE or you'll need to take the list of items that you've already identified and store them in an "exclusion" table or temporary table and use something like a WHERE NOT IN or an exclusive outer join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

nm, ignore

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tnpich Ten Centuries Points: 1223 More actions · Answer 3

Thanks.

Examining the object names that were included, they all contain diagram so a NOT LIKE '%diagram%' seems to work.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

tnpich (10/7/2015)
This still includes some non-user created objects, like the below. See the attachment for details.
fn_diagramobjects
sp_alterdiagram
sp_creatediagram
How can I get rid of these type of objects without filtering on SAO.name LIKE...

These objects are not part of the SQL Server ship catalog. These are created the first time a user creates a database diagram (a pop up menu advises that extra items are required before a diagram may be created 😉 )

By querying for is_ms_shipped you are pulling objects that were not present in the default deployment of the product, not necessarily user created objects

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉