Hey all,
I know I can get a list of tables from a given database with the following query:
select *
from information_schema.tables
How do I go about excluding system tables though?
Thanks,
Sonny
views:
38answers:
2Doesn't seem to work as I expect it to. One of the tables that show up under the System Tables folder in Management Studio, "dtproperties", also shows up in this query and my original. How/why is Management Studio identifying it as a system table?
Sonny Boy
2010-07-09 20:24:08
dtproperties stores the data diagram information I believe, I guess its technically classified as a user table.
kekekela
2010-07-09 20:29:38
Thanks. So I guess I just write my query to specifically ignore that table or there a "cleaner" way around it?
Sonny Boy
2010-07-09 20:30:50
If that's the only table giving you problems then I don't think it'd be too much of a coding horror to just write the query to specifically ignore it.
kekekela
2010-07-09 20:31:54
It is the only one at the moment, I just wanted to make sure there wasn't any other potential problems in the future.
Sonny Boy
2010-07-09 20:36:17
Not that I'm aware of, but then again I wasn't aware that dtproperties was considered a user table either :) I think that is the best that you can do though, the type flag is intended to be used for differentiating between system and user but this is a special case where a management studio designer is generating a table so from MSSQL's perspective its not a system table, as I understand it.
kekekela
2010-07-09 20:36:55