trying to return all tables except the system talbes from a given database

Question

Post reply

trying to return all tables except the system talbes from a given database

BaldingLoopMan

SSCertifiable

Points: 6378
More actions
February 9, 2010 at 8:05 am

#216502

SELECT distinct
*
FROM [C1COMONP002\SQLMonitor].DBA.sys.tables st
join [C1COMONP002\SQLMonitor].DBA.sys.columns AS C
ON C.object_id = st.object_id
--and st.is_published = 1
--i previously added the following constraint "and st.is_published = 1" however it appears this doesnt work in all instances. Doers anyone know how i can modify the above to not get the system tables from a gien db?

Viewing 15 posts - 1 through 15 (of 50 total)

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Cant you just do this and get the results?

SELECT * FROM dbo.sysobjects

WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1

-Roy

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 2

that didnt work. It's still returning the system tables.

Let me clarify. I'm not talking about the tables from the system database.

I'm talking about the system tables listed under a regularly generated db. In my instance i created a DBA database and when i expand it it lists all the tables in the DBA db however at the top there is a folder called "system table" i need my querry not to return those.

I just dont know how to identify them via sql so i can omit them.

GSquared SSC Guru Points: 260824 More actions · Answer 3

sys.tables, by design, only returns user tables. BOL says that right at the top of the description, and I just tested it and confirmed it on two servers. What system tables are you seeing in it?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

The query I gave returns only user tables in all my databases. I tested it again on two different servers. I am not sure why it is showing system tables in your case.

-Roy

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 5

in my instance i want to return all the talbes in my DBA database minus the system tables under the DBA database. However the below returns all the tables plus the dbo.sysdiagrams table which in this instance is indeed in the DBA db however it is listed in the dropdpwn under the "system tables" of the dba database. If you goto a db in mssql mngmt studio and expand a database that has a expandable "system tables" folder in it u'll see that the querry given will return those tables as well. I dont want those tables returned in my querry. If i can identify them i can omit them. I just can't figure out how to identify them.

Follow?

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 6

Roy if you run what u sent me in the msdb database you will see the sysjobhistory, sysdtspackages etc tables are returned. Notice that their under the system tables folder in the msdb database. Am i the only one in the world that is seeing this or are you not looking at the issue properly or am i explaining it incorrectly or a combination the three?

Are they being returned for you as well when you run it on the msdb db?

SELECT * FROM dbo.sysobjects

WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 7

Open up profiler, replicate in the GUI what you want and use the information from the profile trace to get the query that you want.

Shamless self promotion - read my blog http://sirsql.net

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 8

I moved this to the t-sql forum area because it is t-sql i'm looking for and it appears to be getting more traction.

http://www.sqlservercentral.com/Forums/Topic862522-392-1.aspx

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

MSDB if I am not mistaken almost all tables are defined as User tables.

-Roy

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 10

BaldingLoopMan

SSCertifiable

Points: 6378

February 9, 2010 at 12:00 pm

#1116312

Comment removed by the editor.

WayneS SSC Guru Points: 95460 More actions · Answer 11

select *

from sys.tables

where is_ms_shipped = 0

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 12

WayneS that's actually how i initially implimented it. However it did not work in all instances. unfortunatly. I'm going to triple check and get back to you. Attached kinda gives u a better idea into the issue w/ a little background as to what the purpose is for.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 13

use powershell

sqlps dir SQLSERVER:\SQL\<server>\<instance>\Databases\<dbname>\Tables

gives you only user tables, add -force if you want everything.

Shamless self promotion - read my blog http://sirsql.net

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 14

dont think that will work because they actually of table type "user"