locked
Get table list other than system table in databse RRS feed

  • Question

  • I am using SELECT table_name FROM INFORMATION_SCHEMA.TABLES to get all tables in a database.But this list all system tables also. How i can avoid the system tables from above query? I want all tables names in the database except system tables.

    Wednesday, November 21, 2012 5:24 AM

Answers

All replies

  • Add 

    WHERE
        TABLE_TYPE = 'BASE TABLE' AND
        OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
            N'.' +
            QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Marked as answer by IT researcher Wednesday, November 21, 2012 9:15 AM
    Wednesday, November 21, 2012 5:28 AM
  • Thank you Uri Dimant. It worked fine.

    But what is this IsMSShipped property mean?

    Wednesday, November 21, 2012 6:00 AM
  • http://msdn.microsoft.com/en-us/library/ms176105.aspx

    Object created during installation of SQL Server.

    1 = True

    0 = False


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Marked as answer by IT researcher Wednesday, November 21, 2012 9:15 AM
    • Unmarked as answer by IT researcher Wednesday, November 21, 2012 9:15 AM
    Wednesday, November 21, 2012 7:37 AM
  • When run the query you have given against a merge replicated database all user tables resulted in query,no system tables came(which i wanted also). But the system tables are generated during replication setup not during installation of sql server.So i got doubt about IsMSShipped property.So is it just object created during installation or all system objects in sql server?
    Wednesday, November 21, 2012 8:17 AM
  • Only system objects are created during the installation of SQL Server.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance


    Wednesday, November 21, 2012 8:41 AM
  • Try the below script.

    SELECT NAME AS UserTableName  FROM SYS.OBJECTS O WHERE O.TYPE = 'U' And o.type_desc = 'USER_TABLE'
    --------------------------------


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, November 21, 2012 8:57 AM
  • hi ganesh..

    I am not getting what i wanted in your script.It gives all merge replication system tables also.

    Wednesday, November 21, 2012 9:14 AM
  • But that script returns sysdiagrams which is a system table.I can exclude it by specifying that table name table_name<>'sysdiagrams' but it is little odd.can i have a general script where only tables except system tables are returned?How SSMS identifies system tables and user table differently?(sysdiagrams is included in system tables in SSMS but it has is_ms_shipped=0)
    Tuesday, November 27, 2012 2:17 PM