none
Find user defined system tables RRS feed

  • Question

  • Hi Team,

    I would like to find all user defined system tables that are present in System tables folder under database. 

    I have attached below screenshot from table properties of system table. Would you please advise how can i find System Object flag (Yes/No) in table?

    Thursday, April 24, 2014 5:51 PM

Answers

  • A few things are "hard coded" in SSMS to separate user and system objects; "dbo.sysdiagrams" is such a case.

    Run SQL Server Profiler and refresh the view in SSMS, then you get the query to select all "system tables", looks like this:

    exec sp_executesql N'SELECT
    tbl.name AS [Name],
    SCHEMA_NAME(tbl.schema_id) AS [Schema],
    ''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.create_date AS [CreateDate],
    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]
    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)=@_msparam_0)
    ORDER BY
    [Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'1'

    it filters on "extended properties" for microsoft_database_tools_support


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Vick72 Thursday, April 24, 2014 6:32 PM
    Thursday, April 24, 2014 6:15 PM

All replies

  • I would like to find all user defined system tables that are present in System tables folder under database. 

    User defined object can never be a system object!

    All objects under node "System .." are MS shipped object, you can filter them this way, e.g. for tables:

    SELECT *
    FROM sys.tables
    WHERE is_ms_shipped = 1


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, April 24, 2014 5:59 PM
  • Try

    select * from sys.system_objects where is_ms_shipped = 0

    I found this in SQL Server 2012 documentation when I ran a Google search on your question.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 24, 2014 6:00 PM
    Moderator
  • Thanks but I need below highlight table list...
    Thursday, April 24, 2014 6:08 PM
  • A few things are "hard coded" in SSMS to separate user and system objects; "dbo.sysdiagrams" is such a case.

    Run SQL Server Profiler and refresh the view in SSMS, then you get the query to select all "system tables", looks like this:

    exec sp_executesql N'SELECT
    tbl.name AS [Name],
    SCHEMA_NAME(tbl.schema_id) AS [Schema],
    ''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.create_date AS [CreateDate],
    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]
    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)=@_msparam_0)
    ORDER BY
    [Schema] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000)',@_msparam_0=N'1'

    it filters on "extended properties" for microsoft_database_tools_support


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Vick72 Thursday, April 24, 2014 6:32 PM
    Thursday, April 24, 2014 6:15 PM
  • Awesome man! I got it.

    Thank you very much!

    Thursday, April 24, 2014 6:33 PM
  • Hi Olaf,

    Is it possible to put USE DATABASE statement dynamically in this query? 

    I would like to execute this query in different databases. I need to create a process where I can pass the database name and it will list down all the system tables.

    Please advise.

    Tuesday, April 29, 2014 3:29 AM
  • Yes, the simplest way will be

    declare @ExecSQL nvarchar(500);

    set @ExecSQL = quotename(@dbName)+'..sp_executeSQL'

    execute @ExecSQL .... -- the rest of the script as is


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 29, 2014 2:48 PM
    Moderator