[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

skipping sys* tables in a query

Posted on 2008-02-04
2
Medium Priority
?
349 Views
Last Modified: 2008-02-04
Hello,

I'm trying to do a query to select all user tables and apparently SQL server marks system tables like sysarticles and sysdiagrams as user tables. "WHERE ObjectProperty(sysobjects.id,'IsSystemTable') = 0" or "WHERE sysobjects.xtype = 'U'" just don't do. I can't filter by the table names also, because we have many tables that start with "sys". So any suggestions? Thank you very much.
0
Comment
Question by:hccfsa
2 Comments
 
LVL 39

Accepted Solution

by:
appari earned 1000 total points
ID: 20819943
try the following, it returns you all the objects listed under system tables in management studio. i got this from sqlserver profilier. now if you have list of sys tables, you have to exclude these objects by adding a not in to your existing sql.
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]
FROM
sys.tables AS tbl
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)=1)
ORDER BY
[Schema] ASC,[Name] ASC

Open in new window

0
 

Author Comment

by:hccfsa
ID: 20820004
Hehe, why didn't I think of that lol. Thanks. That's exactly what I was looking for.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how the fundamental information of how to create a table.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question