February 9, 2010 at 9:19 am
i need a sql querry that will return all the table names listed under the msdb database that are not listed under the system tables in the msdb db.
Some examples of the system tables in the msdb that i DO NOT WANT RETURNED are sysjobhistory, sysdtspackages. Before you move forward the sysjobhistory and sysdtspackages tables are not true system tables meaning their returned when u run the below. Their ISUserTable = U. Which is why i dont know how to omit them from my querry.
SELECT * FROM dbo.sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1
February 9, 2010 at 9:26 am
That's because they are classified user tables. Try
WHERE OBJECTPROPERTY(id, N'IsMSShipped') = 0
If that doesn't work, can you explain what tables you do what to see and what tables you don't want to see?
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
February 9, 2010 at 9:27 am
Are you trying to distinguish between system tables and tables added by someone else?
Unless you or someone else creates a table in msdb, they are all system tables. Unless I'm missing something.
February 9, 2010 at 9:35 am
Steve Cullen (2/9/2010)
Unless you or someone else creates a table in msdb, they are all system tables. Unless I'm missing something.
The tables in MSDB are not classified system tables (other than the ones like sysrscols, etc, which are true hidden system tables)
SELECT name, type_desc FROM sys.tables
WHERE name LIKE 'sysjob%'
name type_desc
-------------------- -------------------------
sysjobhistory USER_TABLE
sysjobs USER_TABLE
sysjobservers USER_TABLE
sysjobactivity USER_TABLE
sysjobsteps USER_TABLE
sysjobstepslogs USER_TABLE
sysjobschedules USER_TABLE
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
February 9, 2010 at 11:31 am
i've attached a more descriptive doc of what i'm looking for w/ screen shots. Forgive me but i dont know how to manipulate these forum posts vary well. The attached is a word doc called "example.doc"
February 9, 2010 at 11:33 am
sorry for the ambiguity
February 9, 2010 at 11:47 am
So i had the whole proces built then found later that those unwanted tables are being returned and i'm having a bear of a time fionding a way to isolate them so i can omit them. My boss thinks i will have to hard code it to omit them however i know that cant be the case. there must be some way of isolating these tables so i can omit them in my result sets.
February 9, 2010 at 11:53 am
=======================================================================
That's because they are classified user tables. Try
WHERE OBJECTPROPERTY(id, N'IsMSShipped') = 0
If that doesn't work, can you explain what tables you do what to see and what tables you don't want to see?
--------------------------------------------------------------------------------
Gail Shaw
==========================================================================
Funny that's how i coded it originally and it worked for most of the sql instances but not for all. Hence here i am trying to find a resolution.
February 9, 2010 at 11:55 am
from what i can tell there's nothing in the sys.tables nor sysobjects that flags these tables.
February 9, 2010 at 1:12 pm
this was resolved....
=====================================================================
SELECT name
FROM sys.tables t
WHERE t.is_ms_shipped = 0
AND object_ID NOT IN ( SELECT major_ID
FROM sys.extended_properties
WHERE minor_id = 0
AND minor_id = 0
AND class = 1
AND name = N'microsoft_database_tools_support' )
======================================================================
i thought i finally stumped the sql server central gurus.
However it appears "J-F Bergeron" has come up w/ the solution. Not exactly sure yet how yet however, it appears to be returning the proper tables so far. Nice work. It appears i have some more learning to do in refference to extended properties and what all it offers.
Excellent work J-F Bergeron!!!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply