February 9, 2010 at 8:05 am
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?
February 9, 2010 at 8:09 am
Cant you just do this and get the results?
SELECT * FROM dbo.sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1
-Roy
February 9, 2010 at 8:17 am
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.
February 9, 2010 at 8:20 am
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
February 9, 2010 at 8:25 am
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
February 9, 2010 at 8:29 am
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?
February 9, 2010 at 8:39 am
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
February 9, 2010 at 10:00 am
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.
February 9, 2010 at 11:36 am
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://qa.sqlservercentral.com/Forums/Topic862522-392-1.aspx
February 9, 2010 at 11:37 am
MSDB if I am not mistaken almost all tables are defined as User tables.
-Roy
February 9, 2010 at 12:00 pm
Comment removed by the editor.
February 9, 2010 at 12:15 pm
select *
from sys.tables
where is_ms_shipped = 0
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 9, 2010 at 12:19 pm
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.
February 9, 2010 at 12:25 pm
use powershell
sqlps dir SQLSERVER:\SQL\<server>\<instance>\Databases\<dbname>\Tables
gives you only user tables, add -force if you want everything.
February 9, 2010 at 12:32 pm
dont think that will work because they actually of table type "user"
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply