February 9, 2010 at 12:43 pm
There are some extended properties that tell you wether the tables were created for schema-modeling.
Here's what I quickly came up with, someone can tune this:
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' )
Cheers,
J-F
February 9, 2010 at 12:50 pm
BaldingLoopMan (2/9/2010)
Comment removed by request.
I'm sorry, but this comment was totally unnecessary as well as unprofessional.
Personally, I think you owe Roy and the SSC community an apology.
February 9, 2010 at 1:00 pm
i suppose everyone is entitled to their own opinion.
February 9, 2010 at 1:11 pm
BaldingLoopMan (2/9/2010)
i suppose everyone is entitled to their own opinion.
You're right, we are. And we also can decide who we will help and who we won't.
Bye.
February 9, 2010 at 1:11 pm
=====================================================================
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!!!!!
February 9, 2010 at 1:21 pm
BaldingLoopMan (2/9/2010)
=====================================================================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!!!!!
Well, Thanks for the feedback, even though I might not have provided an answer after I read your sarcastic answer to Roy. I think you have to act with a lot more of professionalism on this forum, or you will be the one that will not be benefiting from it.
Most people here are really professional in their answers, and will really try to help you. At least, try to be thankful to their blind guesses. It's not always easy to guess a solution that's not before you...
May you find your way, and act more appropriately.
Have a nice day,
Cheers,
J-F
February 9, 2010 at 1:45 pm
I don't believe in this scenario that you need to use all of those options. Here is a modified query that returns the same result set. The sysdiagrams table will not show up in all databases. And I am still looking for a more accurate script - despite the comments made towards Roy and Lynn, and due to my interest in the solution having been piqued.
SELECT t.name
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
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
February 9, 2010 at 1:48 pm
CirquedeSQLeil (2/9/2010)
I don't believe in this scenario that you need to use all of those options. Here is a modified query that returns the same result set. The sysdiagrams table will not show up in all databases. And I am still looking for a more accurate script - despite the comments made towards Roy and Lynn, and due to my interest in the solution having been piqued.
SELECT t.name
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 might be right for the class and Minor_ID, but I think the N'microsoft_database_tools_support' needs to stay, since there could be an extended property for a table, that is not from microsoft, and you would filter it, if you remove the criteria.
Cheers,
J-F
February 9, 2010 at 1:52 pm
I am testing it. So far, both your query and my query return the exact same results. I also found another extended property on which to limit the results - however it doesn't work in all cases either.
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
February 9, 2010 at 1:55 pm
Removed post as the post which spawned my reply was removed by the editor and reply was no longer needed.
-- Kit
February 9, 2010 at 2:09 pm
You're right, we are. And we also can decide who we will help and who we won't.
Bye.
Lynn Pettis
===========================================================================
Food for thought and I think this is something we should all think about.
No offence to everyone but I too have helped many many people w/ an assortment of sql issues. There have been instances that I too have not read a post fully and answered w/ a haphazard answer and as a result been chastised a little. And rightfully so. Sure maybe I was wrong to chastise Roy. And for that I apologize.
However I would never ever withhold my help because someone did not conform to my professional standards or called me mean names. I find that shallow and conceded. I can understand not providing help because someone doesn't put enough info in a post but to withhold information because of any other sort of grudge is just immature and I’ll be honest I think it’s exactly the kind of elitist attitude that this site doesn’t need and I refuse to be that way toward anyone in need of help regardless of how I’m treated. Use CirquedeSQLeil as an example. Obviously was offended by my remarks however it’s not going to stop him/her from helping me.
In fact, even though lynn and myself disagree in this instance and she probably would never help me again, I would help her at the drop of a hat. I would like to think that everyone would be that way.
I suppose it comes down to why you take the time out of your day to work w/ people in this forum for free. You’re either doing it because ur paying it forward and enjoy helping those in need or your doing it to feel better about your superior elitist knowledge base and will hold it over everyone’s head accordingly. I don’t want that kind of help and I have to say it’s the kind of help that will give this site a bad name.
Just my opinion.
February 9, 2010 at 2:16 pm
My recommendation, though I have a feeling you won't take it, would be to refrain from sarcastic remarks in the future.
-- Kit
===========================================================================
I will do my best to take your advise however sarchasm is in my blood and i appoligize ahead of time if occationally i slip up. My name is balding loop man for crying out loud. I can't ever create a screen name w/ bits of sarcasm. Again i will do my best but your going to have to bare w/ me.
Oh and thanks for the reply. 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. Leaving now. Will get back to you tmrw morning.
Again thanks for all of your help even Lynn's.
February 9, 2010 at 2:24 pm
BaldingLoopMan (2/9/2010)
You're right, we are. And we also can decide who we will help and who we won't.Bye.
Lynn Pettis
===========================================================================
Food for thought and I think this is something we should all think about.
No offence to everyone but I too have helped many many people w/ an assortment of sql issues. There have been instances that I too have not read a post fully and answered w/ a haphazard answer and as a result been chastised a little. And rightfully so. Sure maybe I was wrong to chastise Roy. And for that I apologize.
However I would never ever withhold my help because someone did not conform to my professional standards or called me mean names. I find that shallow and conceded. I can understand not providing help because someone doesn't put enough info in a post but to withhold information because of any other sort of grudge is just immature and I’ll be honest I think it’s exactly the kind of elitist attitude that this site doesn’t need and I refuse to be that way toward anyone in need of help regardless of how I’m treated. Use CirquedeSQLeil as an example. Obviously was offended by my remarks however it’s not going to stop him/her from helping me.
In fact, even though lynn and myself disagree in this instance and she probably would never help me again, I would help her at the drop of a hat. I would like to think that everyone would be that way.
I suppose it comes down to why you take the time out of your day to work w/ people in this forum for free. You’re either doing it because ur paying it forward and enjoy helping those in need or your doing it to feel better about your superior elitist knowledge base and will hold it over everyone’s head accordingly. I don’t want that kind of help and I have to say it’s the kind of help that will give this site a bad name.
Just my opinion.
And the insults continue. Just so you know, I think my wife and daughters would be quite shocked to find out I am a "she."
I give back to the SQL Server community because of what I have learned from those who also give of their time. In the past five years that I have been active on SSC I have learned more than I did learning on my own the previous seven years.
If I choose not to help you in the future it is exactly because of your unprofessional responses. There are ways of telling people that a response was not helpful without getting sarcastic or unprofessional. It happens to be called using tact. Would you have said in person what you said in your post not really knowing the person that was trying to help you? I can tell you my response in person would have made you know I was not appreciative of your comment.
You need to make a choice, accept that you were wrong and apologize and then make an effort to be more professional; or not.
February 9, 2010 at 2:24 pm
BaldingLoopMan (2/9/2010)
...However I would never ever withhold my help because someone did not conform to my professional standards or called me mean names. I find that shallow and conceded. I can understand not providing help because someone doesn't put enough info in a post but to withhold information because of any other sort of grudge is just immature and I’ll be honest I think it’s exactly the kind of elitist attitude that this site doesn’t need and I refuse to be that way toward anyone in need of help regardless of how I’m treated. Use CirquedeSQLeil as an example. Obviously was offended by my remarks however it’s not going to stop him/her from helping me.
Most of us do not take offence at the name calling. That one was a little too far. I took no offence, however felt it inappropriate for a friend to be belittled like that. You have apologized - good enough for me.
I don't find any of the Gurus here to hold themselves as elitists - and they certainly spend a lot of their time helping anybody they can.
I would help her at the drop of a hat. I would like to think that everyone would be that way.
FYI - Lynn is a guy. 😉 And yes, most of us try to help anybody we can - without grudge.
I suppose it comes down to why you take the time out of your day to work w/ people in this forum for free. You’re either doing it because ur paying it forward and enjoy helping those in need or your doing it to feel better about your superior elitist knowledge base and will hold it over everyone’s head accordingly. I don’t want that kind of help and I have to say it’s the kind of help that will give this site a bad name.
We try to self moderate and prevent these kinds of things from happening so that SSC will not get that bad reputation. It is a community, it is a friendship, and it is a lot of people paying it forward.
Just my opinion.
[/quote]
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
February 9, 2010 at 2:41 pm
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.
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply