Home > Joins, SQL Queries, SQL Server, T-SQL > Excluding MS shipped objects in a database.

Excluding MS shipped objects in a database.

Very often we might require to get the list of tables available in a database. Imagine I have a database with a list of tables and a database diagram. This results in the creation of some tables,SPs and indexes shipped by MS( like sysdiagrams,dtproperties). Normally we tend to use the query on “sys.objects” or “sys.tables” and filter the data by using IS_MS_SHIPPED = 0 . This however will select the tables mentioned earlier. This is a flaw in SQL server. to over come this, i used the query as below.

SELECT     T.NAME

FROM  SYS.TABLES T WITH(NOLOCK)
WHERE
(     CASE
WHEN T.IS_MS_SHIPPED = 1 THEN 1
WHEN (
SELECT
MAJOR_ID
FROM
SYS.EXTENDED_PROPERTIES  WITH(NOLOCK)
WHERE
MAJOR_ID = T.OBJECT_ID AND
MINOR_ID = 0 AND
CLASS = 1 AND
NAME = N’MICROSOFT_DATABASE_TOOLS_SUPPORT’)
IS NOT NULL THEN 1
ELSE 0
END

) = 0

This will filter out the unwanted tables. You can use the same filter condition to filter out the SPs, indexes etc,.

Advertisements
  1. March 4, 2014 at 1:03 pm

    If you’ve done office parties or corporate work, make sure to mention that as well.
    Celebrity impersonations, musical gags, and the use of props are just
    some of the things that you could do in your routine.
    After you do all this, you are ready to start performing in front of
    people for money.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: