trying to return all tables except the system talbes from a given database
-
BaldingLoopMan (2/9/2010)
...I still havent figured how ensureing the table doesnt exist in the ext prop table omits the sys tables however i've been busy defending myself. ...It's not guaranteed to do so. Extended properties can be modified by anyone with the proper rights/roles in the database, and then the query will no longer necessarily return the results you are looking for.
The solution given is a work-around hack, and cannot guarantee results in all circumstances. Definitely caveat emptor.
Instead of chastising Roy for his solution, you should probably have defined your requirements completely and clearly. What you originally asked for bears only a slight resemblance to what you eventually clarified. That is most certainly nobody's fault/responsibility but your own.
If you actually need something consistent with what Management Studio returns in its object browser, you should ask the developers of SSMS what criteria they used in defining the tree view. That's the only way you can guarantee duplicated results. Anything else is reverse engineering, and has all the usual problems with hacked systems.
- 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
-
Oh, and as for stumping the gurus: It's pretty easy to stump just about anyone when you can only provide half of your problem-space definition.
You can be just as proud for that as you can for asking "what's seven times?" and being happy when nobody can give the result.
- 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
-
BaldingLoopMan (2/9/2010)
Lynn I do apologize for my comment to Roy. I also apologize for making an improper assumption about you gender. It was not intentional even though it sounded that way.Lynn, do you feel you should apologize as well for flying off the handle and holding the services you provide over someone’s head until they conform to your professional standards? You should. It’s wrong. It’s ok to be wrong. You’re not perfect.
Also your right, as selfish as it seems, that is also a reason why I help people. It's all a learning experience. Often times when I provide assistance I learn something in the process.
So it is about me in the end somewhat.
We disagree on the severity of professionalism though. I think there should be a certain tolerance for levity. That opinion is coming from someone who doesn’t believe there is a one to one relationship between professionalism and intelligence. I perceive developers as more like mechanics than lawyers. It’s talking shop. Again I will do my best but if I offend you later down the road please take it w/ a grain of salt. It isn’t intended to be malicious.
We wear shorts and flip flops to work. That may explain things a bit as far as my sarchasm.
First of all, I didn't fly off the handle. I pointed out that your response to Roy was inappropriate and unprofessional and that you needed to apologize. You said everyone is entitled to their own opinion. I agreed and said we could choose who to help or not.
Not holding anything over your head at all. Is it wrong for me to withold my assistance? No, it isn't. People do it all the time. If you really knew me on this forum, you'd also know that I seem to be the last one to stop trying to help people. It comes down to deciding who to help, some one who treats others professionally or unprofessionally?
All it takes is recognizing you were wrong, and that you have done. Just keep things professional and we won't have any problems.
-
Lynn Pettis (2/9/2010)
BaldingLoopMan (2/9/2010)
Lynn I do apologize for my comment to Roy. I also apologize for making an improper assumption about you gender. It was not intentional even though it sounded that way.Lynn, do you feel you should apologize as well for flying off the handle and holding the services you provide over someone’s head until they conform to your professional standards? You should. It’s wrong. It’s ok to be wrong. You’re not perfect.
Also your right, as selfish as it seems, that is also a reason why I help people. It's all a learning experience. Often times when I provide assistance I learn something in the process.
So it is about me in the end somewhat.
We disagree on the severity of professionalism though. I think there should be a certain tolerance for levity. That opinion is coming from someone who doesn’t believe there is a one to one relationship between professionalism and intelligence. I perceive developers as more like mechanics than lawyers. It’s talking shop. Again I will do my best but if I offend you later down the road please take it w/ a grain of salt. It isn’t intended to be malicious.
We wear shorts and flip flops to work. That may explain things a bit as far as my sarchasm.
First of all, I didn't fly off the handle. I pointed out that your response to Roy was inappropriate and unprofessional and that you needed to apologize. You said everyone is entitled to their own opinion. I agreed and said we could choose who to help or not.
Not holding anything over your head at all. Is it wrong for me to withold my assistance? No, it isn't. People do it all the time. If you really knew me on this forum, you'd also know that I seem to be the last one to stop trying to help people. It comes down to deciding who to help, some one who treats others professionally or unprofessionally?
All it takes is recognizing you were wrong, and that you have done. Just keep things professional and we won't have any problems.
I can vouch for Lynn's wllingness to help people on this forum. I wish I had a dollar for each time someone has suggested to Lynn that he walk away from a difficult poster while Lynn continued to try and help that poster. There are very few on here that have earned, or come close to earning, that reputation.
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
-
BaldingLoopMan (2/9/2010)
--- removed by request ---Ill-judged, possibly offensive, and certainly not funny.
That said, everyone else over-reacted too. Professionalism? This is a forum, not a workplace.
Summary: storm in a tea-cup.
...except for the remark about 'flip-flops'. They're clearly jandals. :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
I have a simple suggestion that would make it easy to exclude the tables for diagramming. Create your own schema instead of using DBO. Then you don't have to worry about it.
Select * from sys.tables where schema_id = [your schema id] -- OR schema_id <> 1 (dbo).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Moring guys. I see this thread has been quite the topic. I thought about this last night and determined that i understand where your coming from Lynn. I've been doing this on my spare time for a few years here and there and you as well as many others who feel they need to speak up against injustice in their eyes have been doing this for quite a bit longer and probably more often to the point where they've become quite attached. I can understand how annoying it must be to have to deal w/ an array of idiotic posting from people who don't put enough info in their posts for a resolution then complain you’re not helping, to people who are just downright disrespectful.
Some of you guys have a right to weight your ideals and principals upon the rest of us simply because the time and energy you’ve put forward on working w/ people on this site. I don't disregard that and commend you for it. So w/ that i will respectfully bow out. Which i have to say is a very very difficult thing to do because i see a lot of errors in judgment here. But again if i had been doing this as long as you guys have i'd probably be right there w/ you and have a totally different perspective.
For the people who are reading this post i do have one thing to say. I will continue to offer my help when applicable. That will never change. Not even if someone treats me in an unprofessional manner. I will not withhold knowledge and experience because someone called my friend a name. I call that acting professionally.
I don't have time or the energy to continue this discussion. I can't afford for my mind to be consumed w/ such things. I'm sure we all have better things to do. So w/ that I’m done arguing and oddly my remark to Roy was removed. Not by me though. Not that it matters.
Alright. Back to the issue at hand.
Well i agree w/ gsquared that the below solution by CirqudeSQLeil is a work around however i wouldn't go as far as calling a man’s help a hack. I would never be that insensitive. 😉 However it's better than what i had. I had to keep the "And ep.name = N'microsoft_database_tools_support'"
constraint. Fortunately the tables under the "system tables" i'm trying to omit will never have extended properties and the is_ms_shipped = 0 appears to take out the rest. Again this is to be run across 70 ish sql instances ranging from 2000-2008 on 50-60 boxes via linked sql. With that being said it will definitely be put though the ringer. I'll be sure and let you know after my testing cycle if it did the job of not. i have to think it will fail to give me the desired results on some of the instances but we'll see. It's crafty nonetheless. I just find it hard to believe that thaere is no easy way for us to isolate those tables. It's crazy that there isn't something in the sys.tables or objects tables. Don't you think?
SELECT t.name, t.*
FROM sys.tables t
Left Outer Join sys.extended_properties ep
On t.object_id = ep.major_id
-- And ep.minor_id = 0
-- And ep.class = 1
And ep.name = N'microsoft_database_tools_support'
Where ep.major_id is null
And t.is_ms_shipped = 0
-
You're not the first to believe that this table behavior is crap at the best. Many people have suggested that it be changed. SMO, is how SSMS knows which tables are user tables or not. TSQL offers no means (that works in all scenarios), except to hard-code the exclusions.
Microsoft may change the way these tables are treated in a future version - we can only hope.
BTW - Thanks for the update. Please keep us posted.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
BaldingLoopMan (2/9/2010)
...Lynn, do you feel you should apologize as well for flying off the handle and holding the services you provide over someone’s head until they conform to your professional standards? You should.
No. NO, no, no and NO. Absolutely NOT.
In fact, Lynn is one of the folks *least* likely to withhold aid because of bad behavior. It has taken us almost a year to persuade him that like most of the rest of us, he has to stop rewarding bad behavior, or the bad behavior will never stop. In short, "feeding the Trolls" is always a mistake because it just encourages the trolls to keep being trolls.
And I am not talking about grudges, hissy fits, differences of opinion or whatever, I am talking about behavior. We will help anybody who behaves in a civil manner. All anybody who wants help has to do is behave.
It’s wrong. It’s ok to be wrong. You’re not perfect.
None of us are perfect, but in this case we are not wrong either. Because YOU do NOT have a right to receive help from us or this site. And no, that is not my opinion it is a FACT. Receiving free help from strangers is nobody's right, it is just great good fortune on your part.
Further it IS our right to refuse to help anyone here if we think that they are behaving badly, and more, it is our responsibility[/b] to refuse aid if we believe that such aid will encourage more bad behavior. Again, not opinions. These are facts.
Deal with them.{EDIT: I am withdrawing the last sentence. It was a bit over the line and personal. I apologize for that.}
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
One of the most fundamental rights any human being has (or has been denied) is the right to choose with whom to associate. If Lynn, or anyone else, chooses not to associate with you, in this case by deciding not to further help you, that is merely an exercise of that right.
If you do not get to choose the people you will help, and what help you will provide, then you are a slave. It's really as simple as that. There is no such thing as "involuntary charity". It's just slavery by a different name.
- 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
-
Well said, Gus.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
You can either query the Object Catalog View or the Information Schema View.
to return user tables and their columns:
-- Object Catalog
SELECT T.NAME AS, C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS, CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE’
-- Information Schema
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS
SQL Server Database Administrator
-
MarlonRibunal (2/10/2010)
You can either query the Object Catalog View or the Information Schema View.to return user tables and their columns:
-- Object Catalog
SELECT T.NAME AS, C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS, CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE’
-- Information Schema
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS
That query is fine to return all tables marked as 'User Tables'. It doesn't meet the requirements for this thread though. Sysdiagrams is considered a 'User Table' even though it is a system table. Thus your query still returns the sysdiagrmas and dtproperties tables.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Running a profiler trace for the SQL issued by SSMS (2008, 10.0.2757) gives the following batch (very slight changes made to translate the original sp_executesql-type syntax):
DECLARE @Param0 NVARCHAR(4000);
DECLARE @Param1 NVARCHAR(4000);
DECLARE @is_policy_automation_enabled BIT;
SET @Param0 = 1 -- 0 = 'Tables', 1 = 'System Tables'
SET @Param1 = N'AdventureWorks' -- also you must set the database context to this database, for example with a USE statement
USE AdventureWorks;
SET @is_policy_automation_enabled = (SELECT CONVERT(bit, current_value)
FROM msdb.dbo.syspolicy_configuration
WHERE name = 'Enabled')
SELECT
'Server[@Name=' + quotename(CAST(
serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/Table[@Name=' + quotename(tbl.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(tbl.schema_id),'''') + ']' AS [Urn],
tbl.name AS [Name],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
CAST(
case
when tbl.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = tbl.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [IsSystemObject],
tbl.create_date AS [CreateDate],
stbl.name AS [Owner],
case when 1=@is_policy_automation_enabled and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like 'Server' + '/Database\[@ID=' + convert(nvarchar(20),dtb.database_id) + '\]'+ '/Table\[@ID=' + convert(nvarchar(20),tbl.object_id) + '\]%' ESCAPE '\') then 1 else 0 end AS [PolicyHealthState]
FROM
master.sys.databases AS dtb,
sys.tables AS tbl
INNER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, 'OwnerId')))
WHERE
(CAST(
case
when tbl.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = tbl.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit)=@Param0)and((db_name()=@Param1)and(dtb.name=db_name()))
ORDER BY
[Schema] ASC,[Name] ASC
I have set this up to work with the AdventureWorks sample database. Set the @Param0 and @Param1 variables as noted in the comments, and adjust the following USE command to match @Param1.
I should mention that this type of approach is extremely fragile and I don't recommend it.
There is no permanently robust way to identify the tables in question as 'system tables'. The behaviour of SSMS could change at any time. Whether tables appear as 'user' or 'system' appears to be an implementation detail which you should not rely on.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Hello all
I got a topic. A database management system (or DBMS) is essentially nothing more than a computerized data-keeping system. Users of the system are given facilities to perform several kinds of operations on such a system for either manipulation of the data in the database or the management of the database structure itself. Database Management Systems (DBMSs) are categorized according to their data structures or types.
Thanks
Viewing 15 posts - 31 through 45 (of 50 total)
You must be logged in to reply to this topic. Login to reply