querying-the-sql-server-system-catalog-faq.yml

### YamlMime:FAQ
metadata:
  description: "Querying the SQL Server System Catalog FAQ"
  title: "Querying the SQL Server System Catalog FAQ | Microsoft Docs"
  ms.custom: ""
  ms.date: "03/14/2017"
  ms.service: sql
  ms.prod_service: "database-engine"
  ms.reviewer: ""
  ms.subservice: system-objects
  ms.topic: "reference"
  dev_langs: 
    - "TSQL"
  helpviewer_keywords: 
    - "catalog views [SQL Server], examples"
    - "metadata [SQL Server], frequently asked questions"
    - "metadata [SQL Server], example queries"
    - "system catalogs [SQL Server], example queries"
    - "catalog views [SQL Server], frequently asked questions"
  author: rwestMSFT
  ms.author: randolphwest
title: Querying the SQL Server System Catalog FAQ
summary: |
  [!INCLUDE[sql-asdb-asdbmi-asa-fabricse-fabricdw](../../includes/applies-to-version/sql-asdb-asdbmi-asa-fabricse-fabricdw.md)]
  
    This topic contains a list of frequently asked questions. The answers to these questions are queries that are based on catalog views.  
    

sections:
  - name: Ignored
    questions:
      - question:  Frequently Asked Questions  
        answer: |
          <a name="_TOP"></a> The sections below list frequently asked questions by category.  
            
          **Data Types**
            
          -   [How do I find the data types of the columns of a specified table?](#_FAQ7)  
            
          -   [How do I find the LOB data types of a specified table?](#_FAQ14)  
            
          -   [How do I find the columns that depend on a specified data type?](#_FAQ22)  
            
          -   [How do I find the computed columns that depend on a specified CLR user-defined type or alias data type?](#_FAQ23)  
            
          -   [How do I find the parameters that depend on a specified CLR user-defined type or alias type?](#_FAQ24)  
            
          -   [How do I find the CHECK constraints that depend on a specified CLR user-defined type?](#_FAQ25)  
            
          -   [How do I find the views, Transact-SQL functions, and Transact-SQL stored procedures that depend on a specified CLR user-defined type or alias type?](#_FAQ26)  
            
          **Tables, Indexes, Views, and Constraints** 
            
          -   [How do I find all the user-defined tables in a specified database?](#_FAQ31)  
            
          -   [How do I find all the tables that do not have a clustered index in a specified database?](#_FAQ1)  
            
          -   [How do I find all the tables that do not have an index?](#_FAQ4)  
            
          -   [How do I find all the tables that do not have a primary key?](#_FAQ3)  
            
          -   [How do I find all the tables that have an identity column?](#_FAQ5)  
            
          -   [How do I find all the tables and indexes that are partitioned?](#_FAQ32)  
            
          -   [How do I find all views in a database?](#_FAQ13)  
            
          -   [How do I find the definition of a view?](#_FAQ35)  
            
          -   [How do I find all the entities that have been modified in the last N days?](#_FAQ6)  
            
          -   [How do I find the columns of a primary key for a specified table?](#_FAQ16)  
            
          -   [How do I find the columns of a foreign key for a specified table?](#_FAQ17)  
            
          -   [How do I determine if a column is used in a computed column expression?](#_FAQ20)  
            
          -   [How do I find all the columns that are used in a computed column expression?](#_FAQ21)  
            
          -   [How do I find all the constraints for a specified table?](#_FAQ27)  
            
          -   [How do I find all the indexes for a specified table?](#_FAQ28)  
            
          -   [How do I find all the tables that have a specified column name?](#_FAQ30)  
            
          -   [How do I find all the statistics on a specified object?](#_FAQ33)  
            
          -   [How do I find all the statistics and statistics columns on a specified object?](#_FAQ34)  
            
          **Modules (Stored Procedures, User-Defined Functions, and Triggers)** 
            
          -   [How do I find all the stored procedures in a database?](#_FAQ9)  
            
          -   [How do I find all the user-defined functions in a database?](#_FAQ12)  
            
          -   [How do I find the parameters for a specified stored procedure or function?](#_FAQ10)  
            
          -   [How do I find the dependencies on a specified function?](#_FAQ8)  
            
          -   [How do I view the definition of a module?](#_FAQ15)  
            
          -   [How do I view the definition of a server-level trigger?](#_FAQ19)  
            
          **Schemas, Users, Roles, and Permissions**
            
          -   [How do I find all the owners of entities contained in a specified schema?](#_FAQ2)  
            
          -   [How do I find the permissions granted or denied to a specified principal?](#_FAQ18)  
            
      - question: Answers  
        answer: |
          <a name="_FAQ1"></a> **How do I find all the tables that do not have a clustered index in a specified database?**
          
          Before you run the following queries, replace `<database_name>` with a valid database name.
            
          ```sql  
          SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name  
          FROM sys.tables AS t  
          WHERE NOT EXISTS   
             (  
               SELECT * FROM sys.indexes AS i  
               WHERE i.object_id = t.object_id  
               AND i.type = 1  -- or type_desc = 'CLUSTERED'  
             )  
          ORDER BY schema_name, table_name;  
          GO  
            
          ```  
            
           Or, you can use the `OBJECTPROPERTY` function as shown in the following example.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS table_name  
          FROM sys.tables   
          WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0  
          ORDER BY schema_id, name;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ2"></a> **How do I find all the owners of entities contained in a specified schema?**
          
          Before you run the following query, replace `<database_name>` and `<schema_name>` with valid names.
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT 'OBJECT' AS entity_type  
              ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name  
              ,name   
          FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'  
          UNION   
          SELECT 'TYPE' AS entity_type  
              ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name  
              ,name   
          FROM sys.types WHERE SCHEMA_NAME(schema_id) = '<schema_name>'   
          UNION  
          SELECT 'XML SCHEMA COLLECTION' AS entity_type   
              ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name  
              ,xsc.name   
          FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s  
              ON s.schema_id = xsc.schema_id  
          WHERE s.name = '<schema_name>';  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ3"></a> **How do I find all the tables that do not have a primary key?**
          
          Before you run the following queries, replace `<database_name>` with a valid database name.
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT SCHEMA_NAME(t.schema_id) AS schema_name  
              ,t.name AS table_name  
          FROM sys.tables t   
          WHERE object_id NOT IN   
             (  
              SELECT parent_object_id   
              FROM sys.key_constraints   
              WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT' -- or type = 'PK'  
              );  
          GO  
            
          ```  
            
           Or, you can run the following query.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT SCHEMA_NAME(schema_id) AS schema_name  
              ,name AS table_name   
          FROM sys.tables   
          WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0  
          ORDER BY schema_name, table_name;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ4"></a> **How do I find all the tables that do not have an index?**
          
          Before you run the following query, replace `<database_name>` with a valid database name.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT SCHEMA_NAME(schema_id) AS schema_name  
              ,name AS table_name  
          FROM sys.tables   
          WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0  
          ORDER BY schema_name, table_name;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ5"></a> **How do I find all the tables that have an identity column?**
          
          Before you run the following query, replace `<database_name>` with a valid database name.
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT SCHEMA_NAME(schema_id) AS schema_name  
              , t.name AS table_name  
              , c.name AS column_name  
          FROM sys.tables AS t  
          JOIN sys.identity_columns c ON t.object_id = c.object_id  
          ORDER BY schema_name, table_name;  
          GO  
            
          ```  
            
           Or, you can run the following query.  
            
          > [!NOTE]  
          >  This query does not return the name of the columns.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT SCHEMA_NAME(schema_id) AS schema_name  
              ,name AS table_name   
          FROM sys.tables   
          WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1  
          ORDER BY schema_name, table_name;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ7"></a> **How do I find the data types of the columns of a specified table?** 
          
          Before you run the following query, replace `<database_name>` and `<schema_name.table_name>` with valid names.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT c.name AS column_name  
              ,c.column_id  
              ,SCHEMA_NAME(t.schema_id) AS type_schema  
              ,t.name AS type_name  
              ,t.is_user_defined  
              ,t.is_assembly_type  
              ,c.max_length  
              ,c.precision  
              ,c.scale  
          FROM sys.columns AS c   
          JOIN sys.types AS t ON c.user_type_id=t.user_type_id  
          WHERE c.object_id = OBJECT_ID('<schema_name.table_name>')  
          ORDER BY c.column_id;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ8"></a> **How do I find the dependencies on a specified function?** 
          
          Before you run the following query, replace `<database_name>` and `<schema_name.function_name>` with valid names.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_NAME(object_id) AS referencing_object_name  
              ,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name  
              ,*  
          FROM sys.sql_dependencies  
          WHERE referenced_major_id = OBJECT_ID('<schema_name.function_name>')  
          ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ9"></a> **How do I find all the stored procedures in a database?** 
          
          Before you run the following query, replace `<database_name>` with a valid name.  
            
          ```sql  
            
          USE <database_name>;  
          GO  
          SELECT name AS procedure_name   
              ,SCHEMA_NAME(schema_id) AS schema_name  
              ,type_desc  
              ,create_date  
              ,modify_date  
          FROM sys.procedures;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ10"></a> **How do I find the parameters for a specified stored procedure or function?** 
          
          Before you run the following query, replace `<database_name>` and `<schema_name.object_name>` with valid names.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT SCHEMA_NAME(schema_id) AS schema_name  
              ,o.name AS object_name  
              ,o.type_desc  
              ,p.parameter_id  
              ,p.name AS parameter_name  
              ,TYPE_NAME(p.user_type_id) AS parameter_type  
              ,p.max_length  
              ,p.precision  
              ,p.scale  
              ,p.is_output  
          FROM sys.objects AS o  
          INNER JOIN sys.parameters AS p ON o.object_id = p.object_id  
          WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')  
          ORDER BY schema_name, object_name, p.parameter_id;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ12"></a> **How do I find all the user-defined functions in a database?** 
          
          Before you run the following query, replace `<database_name>` with a valid database name.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT name AS function_name   
            ,SCHEMA_NAME(schema_id) AS schema_name  
            ,type_desc  
            ,create_date  
            ,modify_date  
          FROM sys.objects  
          WHERE type_desc LIKE '%FUNCTION%';  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ13"></a> **How do I find all views in a database?** 
          
          Before you run the following query, replace `<database_name>` with a valid database name.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT name AS view_name   
            ,SCHEMA_NAME(schema_id) AS schema_name  
            ,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed  
            ,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable  
            ,create_date  
            ,modify_date  
          FROM sys.views;  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ6"></a> **How do I find all the entities that have been modified in the last N days?** 
          
          Before you run the following query, replace `<database_name>` and `<n_days>` with valid values.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT name AS object_name   
            ,SCHEMA_NAME(schema_id) AS schema_name  
            ,type_desc  
            ,create_date  
            ,modify_date  
          FROM sys.objects  
          WHERE modify_date > GETDATE() - <n_days>  
          ORDER BY modify_date;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ14"></a> **How do I find the LOB data types of a specified table?** 
          
          Before you run the following query, replace `<database_name>` and `<schema_name.table_name>` with valid names.  
            
          ```sql  
            
          USE <database_name>;  
          GO  
          SELECT name AS column_name   
              ,column_id   
              ,TYPE_NAME(user_type_id) AS type_name  
              ,max_length  
              ,CASE   
                 WHEN max_length = -1 AND TYPE_NAME(user_type_id) <> 'xml'  
                      THEN 1  
                      ELSE 0  
               END AS [(max)]  
          FROM sys.columns  
          WHERE object_id=OBJECT_ID('<schema_name.table_name>')   
              AND ( TYPE_NAME(user_type_id) IN ('xml','text', 'ntext','image')  
                   OR (TYPE_NAME(user_type_id) IN ('varchar','nvarchar','varbinary')  
                   AND max_length = -1)  
                  );  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ15"></a> **How do I view the definition of a module?** 
          
          Before you run the following query, replace `<database_name>` and `<schema_name.object_name>` with valid names.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT definition  
          FROM sys.sql_modules  
          WHERE object_id = OBJECT_ID('<schema_name.object_name>');  
          GO  
            
          ```  
            
           Or, you can use the `OBJECT_DEFINITION` function as shown in the following example.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_DEFINITION (OBJECT_ID('<schema_name.object_name>')) AS ObjectDefinition;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ19"></a> **How do I view the definition of a server-level trigger?** 
            
          ```sql  
          SELECT definition  
          FROM sys.server_sql_modules;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ16"></a> **How do I find the columns of a primary key for a specified table?** 
          
          Before you run the following query, replace `<database_name>` and `<schema_name.table_name>` with valid names.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT i.name AS index_name  
              ,ic.index_column_id  
              ,key_ordinal  
              ,c.name AS column_name  
              ,TYPE_NAME(c.user_type_id)AS column_type   
              ,is_identity  
          FROM sys.indexes AS i  
          INNER JOIN sys.index_columns AS ic   
              ON i.object_id = ic.object_id AND i.index_id = ic.index_id  
          INNER JOIN sys.columns AS c   
              ON ic.object_id = c.object_id AND c.column_id = ic.column_id  
          WHERE i.is_primary_key = 1   
              AND i.object_id = OBJECT_ID('<schema_name.table_name>');  
          GO  
            
          ```  
            
           Or, you can use the `COL_NAME` function as shown in the following example.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT i.name AS index_name  
              ,COL_NAME(ic.object_id,ic.column_id) AS column_name  
              ,ic.index_column_id  
              ,key_ordinal  
          FROM sys.indexes AS i  
          INNER JOIN sys.index_columns AS ic   
              ON i.object_id = ic.object_id AND i.index_id = ic.index_id  
          WHERE i.is_primary_key = 1   
              AND i.object_id = OBJECT_ID('<schema_name.table_name>');  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ17"></a> **How do I find the columns of a foreign key for a specified table?** 
          
          Before you run the following query, replace `<database_name>` and `<schema_name.table_name>` with valid names.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT   
              f.name AS foreign_key_name  
             ,OBJECT_NAME(f.parent_object_id) AS table_name  
             ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name  
             ,OBJECT_NAME (f.referenced_object_id) AS referenced_object  
             ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name  
             ,is_disabled  
             ,delete_referential_action_desc  
             ,update_referential_action_desc  
          FROM sys.foreign_keys AS f  
          INNER JOIN sys.foreign_key_columns AS fc   
             ON f.object_id = fc.constraint_object_id   
          WHERE f.parent_object_id = OBJECT_ID('<schema_name.table_name>');  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ18"></a> **How do I find the permissions granted or denied to a specified principal?** 
           The following example creates a function to return the name of the entity on which the permissions are checked. The function is invoked in the queries that follow. The function must be created in every database in which you want to check permissions.  
            
          ```sql  
          -- Create a function to return the name of the entity on which the permissions are checked.  
          IF OBJECT_ID (N'dbo.entity_instance_name', N'FN') IS NOT NULL  
              DROP FUNCTION dbo.entity_instance_name;  
          GO  
          CREATE FUNCTION dbo.entity_instance_name(@class_desc nvarchar(60), @major_id int)   
          RETURNS sysname AS  
          BEGIN  
              DECLARE @the_entity_name sysname  
              SELECT @the_entity_name = CASE  
                  WHEN @class_desc = 'DATABASE' THEN DB_NAME()  
                  WHEN @class_desc = 'SCHEMA' THEN SCHEMA_NAME(@major_id)  
                  WHEN @class_desc = 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(@major_id)  
                  WHEN @class_desc = 'DATABASE_PRINCIPAL' THEN USER_NAME(@major_id)  
                  WHEN @class_desc = 'ASSEMBLY' THEN   
                      (SELECT name FROM sys.assemblies WHERE assembly_id=@major_id)  
                  WHEN @class_desc = 'TYPE' THEN TYPE_NAME(@major_id)  
                  WHEN @class_desc = 'XML_SCHEMA_COLLECTION' THEN   
                      (SELECT name FROM sys.xml_schema_collections  
                        WHERE xml_collection_id=@major_id)  
                  WHEN @class_desc = 'MESSAGE_TYPE' THEN   
                      (SELECT name FROM sys.service_message_types WHERE message_type_id=@major_id)  
                  WHEN @class_desc = 'SERVICE_CONTRACT' THEN   
                     (SELECT name FROM sys.service_contracts  
                        WHERE service_contract_id=@major_id)  
                  WHEN @class_desc = 'SERVICE' THEN  
                    (SELECT name FROM sys.services WHERE service_id=@major_id)  
                  WHEN @class_desc = 'REMOTE_SERVICE_BINDING' THEN  
                    (SELECT name FROM sys.remote_service_bindings  
                       WHERE remote_service_binding_id=@major_id)  
                  WHEN @class_desc = 'ROUTE' THEN  
                    (SELECT name FROM sys.routes WHERE route_id=@major_id)  
                  WHEN @class_desc = 'FULLTEXT_CATALOG' THEN  
                    (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id=@major_id)  
                  WHEN @class_desc = 'SYMMETRIC_KEY' THEN  
                    (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id=@major_id)  
                  WHEN @class_desc = 'CERTIFICATE' THEN  
                    (SELECT name FROM sys.certificates WHERE certificate_id=@major_id)  
                  WHEN @class_desc = 'ASYMMETRIC_KEY' THEN  
                    (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id=@major_id)  
                  WHEN @class_desc = 'SERVER' THEN   
                       (SELECT name FROM sys.servers WHERE server_id=@major_id)  
                  WHEN @class_desc = 'SERVER_PRINCIPAL' THEN SUSER_NAME(@major_id)  
                  WHEN @class_desc = 'ENDPOINT' THEN   
                       (SELECT name FROM sys.endpoints WHERE endpoint_id=@major_id)        
                  ELSE '?'  
              END  
              RETURN @the_entity_name  
          END;  
          GO  
          -- Return server-level permissions for the user.  
          SELECT class  
              ,class_desc  
              ,dbo.entity_instance_name(class_desc, major_id) AS entity_name   
              ,minor_id  
              ,SUSER_NAME(grantee_principal_id) AS grantee  
              ,SUSER_NAME(grantor_principal_id) AS grantor  
              ,type  
              ,permission_name  
              ,state_desc   
          FROM sys.server_permissions   
          WHERE grantee_principal_id = SUSER_ID('public');  
          GO  
          -- Return database-level permissions for the user.  
          SELECT class  
              ,class_desc  
              ,dbo.entity_instance_name(class_desc , major_id) AS entity_name   
              ,minor_id  
              ,USER_NAME(grantee_principal_id) AS grantee  
              ,USER_NAME(grantor_principal_id) AS grantor  
              ,type  
              ,permission_name  
              ,state_desc     
          FROM  sys.database_permissions   
          WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('public');  
          GO  
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ20"></a> **How do I determine if a column is used in a computed column expression?** 
          
          Before you run the following query, replace `<database_name>`, `<schema_name.table_name>`, and `<column_name`> with valid names.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_NAME(object_id) AS object_name  
              ,COL_NAME(object_id, column_id) AS computed_column   
              ,class_desc  
              ,is_selected  
              ,is_updated  
              ,is_select_all  
          FROM sys.sql_dependencies  
          WHERE referenced_major_id = OBJECT_ID('<schema_name.table_name>')  
              AND referenced_minor_id = COLUMNPROPERTY(referenced_major_id, '<column_name>', 'ColumnId')  
              AND class = 1;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ21"></a> **How do I find all the columns that are used in a computed column expression?** 
          
          Before you run the following query, replace `<database_name>` with a valid name.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_NAME(d.referenced_major_id) AS object_name  
              ,COL_NAME(d.referenced_major_id, d.referenced_minor_id) AS column_name  
              ,OBJECT_NAME(referenced_major_id) AS dependent_object_name   
              ,COL_NAME(d.object_id, d.column_id) AS dependent_computed_column  
              ,cc.definition AS computed_column_definition  
          FROM sys.sql_dependencies AS d  
          JOIN sys.computed_columns AS cc   
              ON cc.object_id = d.object_id AND cc.column_id = d.column_id AND d.object_id=d.referenced_major_id       
          WHERE d.class = 1  
          ORDER BY object_name, column_name;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ22"></a> **How do I find the columns that depend on a specified CLR user-defined type or alias type?** 
          
          Before you run the following query, replace `<database_name>` with a valid name and `<schema_name.data_type_name>` with a valid, schema-qualified CLR user-defined type, or schema-qualified alias type name. The following query requires membership in the **db_owner** role or permissions to see all dependent column and computed column metadata in the database.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_NAME(object_id) AS object_name   
              ,c.name AS column_name   
              ,SCHEMA_NAME(t.schema_id) AS schema_name  
              ,TYPE_NAME(c.user_type_id) AS user_type_name  
              ,c.max_length  
              ,c.precision  
              ,c.scale  
              ,c.is_nullable  
              ,c.is_computed  
          FROM sys.columns AS c  
          INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id  
          WHERE c.user_type_id = TYPE_ID('<schema_name.data_type_name>');   
          GO  
            
          ```  
            
           The following query returns a restricted and narrow view of columns dependent on a CLR user-defined type or alias, but the result set is visible to the **public** role. You can use this query if you have granted REFERENCE permissions on your user-defined type to others and you do not have permission to view the metadata of the objects others have created that use the type.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_NAME(object_id) AS object_name   
              ,COL_NAME(object_id, column_id) AS column_name  
              ,TYPE_NAME(user_type_id) AS user_type  
          FROM sys.column_type_usages  
          WHERE user_type_id = TYPE_ID('<schema_name.data_type_name>');  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ23"></a> **How do I find the computed columns that depend on a specified CLR user-defined type or alias type?** 
          
          Before you run the following query, replace `<database_name>` with a valid name and `<schema_name.data_type_name>` with a valid, schema-qualified CLR user-defined type, alias type name.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_NAME(object_id) AS object_name  
              ,COL_NAME(object_id, column_id) AS column_name  
          FROM sys.sql_dependencies  
          WHERE referenced_major_id = TYPE_ID('<schema_name.data_type_name>')  
              AND class = 2 -- schema-bound references to type  
              AND OBJECTPROPERTY(object_id, 'IsTable') = 1;   -- exclude non-table dependencies  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ24"></a> **How do I find the parameters that depend on a specified CLR user-defined type or alias type?** 
          
          Before you run the following query, replace `<database_name>` with a valid name and `<schema_name.data_type_name>` with a valid, schema-qualified CLR user-defined type, alias type name. The following query requires membership in the **db_owner** role or permissions to see all dependent column and computed column metadata in the database.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_NAME(object_id) AS object_name  
              ,NULL AS procedure_number  
              ,name AS param_name  
              ,parameter_id AS param_num  
              ,TYPE_NAME(p.user_TYPE_ID) AS type_name  
          FROM sys.parameters AS p  
          WHERE p.user_TYPE_ID = TYPE_ID('<schema_name.data_type_name>')  
          UNION   
          SELECT OBJECT_NAME(object_id) AS object_name  
              ,procedure_number  
              ,name AS param_name  
              ,parameter_id AS param_num  
              ,TYPE_NAME(p.user_TYPE_ID) AS type_name  
          FROM sys.numbered_procedure_parameters AS p  
          WHERE p.user_TYPE_ID = TYPE_ID('<schema_name.data_type_name>')  
          ORDER BY object_name, procedure_number, param_num;  
          GO  
            
          ```  
            
           The following query returns a restricted and narrow view of parameters that depend on a CLR user-defined type or alias, but the result set is visible to the **public** role. You can use this query if you have granted REFERENCE permissions on your user-defined type to others and you do not have permission to view the metadata of the objects others have created that use the type.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_NAME(object_id) AS object_name  
              ,parameter_id  
              ,TYPE_NAME(user_type_id) AS type_name  
          FROM sys.parameter_type_usages   
          WHERE user_type_id = TYPE_ID('<schema_name.data_type_name>');  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ25"></a> **How do I find the CHECK constraints that depend on a specified CLR user-defined type?** 
          
          Before you run the following query, replace `<database_name>` with a valid name and `<schema_name.data_type_name>` with a valid, schema-qualified CLR user-defined type name.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT SCHEMA_NAME(o.schema_id) AS schema_name  
              ,OBJECT_NAME(o.parent_object_id) AS table_name  
              ,OBJECT_NAME(o.object_id) AS constraint_name  
          FROM sys.sql_dependencies AS d  
          JOIN sys.objects AS o ON o.object_id = d.object_id  
          WHERE referenced_major_id = TYPE_ID('<schema_name.data_type_name>')  
              AND class = 2 -- schema-bound references to type  
              AND OBJECTPROPERTY(o.object_id, 'IsCheckCnst') = 1; -- exclude non-CHECK dependencies  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ26"></a> **How do I find the views, Transact-SQL functions, and Transact-SQL stored procedures that depend on a specified CLR user-defined type or alias type?** 
          
          Before you run the following query, replace `<database_name>` with a valid name and `<schema_name.data_type_name>` with a valid, schema-qualified CLR user-defined type, alias type name.  
            
           The parameters defined in a function or procedure are implicitly schema bound. Therefore, parameters that depend on a CLR user-defined type or alias type can be viewed by using the [sys.sql_dependencies](../../relational-databases/system-catalog-views/sys-sql-dependencies-transact-sql.md) catalog view. Procedures and triggers are not schema bound. This means that dependencies between any expression defined in the body of the procedure or trigger and a CLR user-defined type or alias type is not maintained. Schema bound views and schema bound user-defined functions that have expressions that depend on a CLR user-defined type or alias type are maintained in the **sys.sql_dependencies** catalog view. Dependencies between types and CLR functions and CLR procedures are not maintained.  
            
           The following query returns all schema-bound dependencies in views, [!INCLUDE[tsql](../../includes/tsql-md.md)] functions, and [!INCLUDE[tsql](../../includes/tsql-md.md)] stored procedures for a specified CLR user-defined type or alias type.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT SCHEMA_NAME(o.schema_id) AS dependent_object_schema  
            ,OBJECT_NAME(o.object_id) AS dependent_object_name  
            ,o.type_desc AS dependent_object_type  
            ,d.class_desc AS kind_of_dependency  
            ,TYPE_NAME (d.referenced_major_id) AS type_name  
          FROM sys.sql_dependencies AS d   
          JOIN sys.objects AS o  
            ON d.object_id = o.object_id  
            AND o.type IN ('FN','IF','TF', 'V', 'P')  
          WHERE d.class = 2 -- dependencies on types  
            AND d.referenced_major_id = TYPE_ID('<schema_name.data_type_name>')  
          ORDER BY dependent_object_schema, dependent_object_name;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ27"></a> **How do I find all the constraints for a specified table?** 
          
          Before you run the following query, replace `<database_name>` and `<schema_name.table_name>` with valid names.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_NAME(object_id) as constraint_name  
              ,SCHEMA_NAME(schema_id) AS schema_name  
              ,OBJECT_NAME(parent_object_id) AS table_name  
              ,type_desc  
              ,create_date  
              ,modify_date  
              ,is_ms_shipped  
              ,is_published  
              ,is_schema_published  
          FROM sys.objects  
          WHERE type_desc LIKE '%CONSTRAINT'   
              AND parent_object_id = OBJECT_ID('<schema_name.table_name>');  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ28"></a> **How do I find all the indexes for a specified table?** 
          
          Before you run the following query, replace `<database_name>` and `<schema_name.table_name>` with valid names.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT i.name AS index_name  
              ,i.type_desc  
              ,is_unique  
              ,ds.type_desc AS filegroup_or_partition_scheme  
              ,ds.name AS filegroup_or_partition_scheme_name  
              ,ignore_dup_key  
              ,is_primary_key  
              ,is_unique_constraint  
              ,fill_factor  
              ,is_padded  
              ,is_disabled  
              ,allow_row_locks  
              ,allow_page_locks  
          FROM sys.indexes AS i  
          INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id  
          WHERE is_hypothetical = 0 AND i.index_id <> 0   
          AND i.object_id = OBJECT_ID('<schema_name.table_name>');  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ30"></a> **How do I find all the objects that have a specified column name?** 
          
          Before you run the following query, replace `<database_name>` and `<column_name>` with valid names.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_NAME(object_id)  
          FROM sys.columns  
          WHERE name = '<column_name>';  
          GO  
            
          ```  
            
           Or  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT SCHEMA_NAME(o.schema_id) AS schema_name   
              ,o.name AS object_name  
              ,type_desc  
          FROM sys.objects AS o  
          INNER JOIN sys.columns AS c ON o.object_id = c.object_id  
          WHERE c.name = '<column_name>';  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ31"></a> **How do I find all the user-defined tables in a specified database?** 
          
          Before you run the following query, replace `<database_name>` with a valid name.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT *   
          FROM sys.tables;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ32"></a> **How do I find all the tables and indexes that are partitioned?** 
          
          Before you run the following query, replace `<database_name>` with a valid name.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT SCHEMA_NAME(o.schema_id) AS schema_name  
              ,OBJECT_NAME(p.object_id) AS table_name  
              ,i.name AS index_name  
              ,p.partition_number  
              ,rows   
          FROM sys.partitions AS p  
          INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id  
          INNER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id  
          INNER JOIN sys.objects AS o ON o.object_id = i.object_id  
          ORDER BY index_name, partition_number;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ33"></a> **How do I find all the statistics on a specified object?** 
          
          Before you run the following query, replace `<database_name>` with a valid name and `<schema_name.object_name>` with a valid table, indexed view, or table-valued function name.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT name AS statistics_name  
              ,stats_id  
              ,auto_created  
              ,user_created  
              ,no_recompute  
          FROM sys.stats  
          WHERE object_id = OBJECT_ID('<schema_name.object_name>');  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ34"></a> **How do I find all the statistics and statistics columns on a specified object?** 
          
          Before you run the following query, replace `<database_name>` with a valid name and `<schema_name.object_name>` with a valid table, indexed view, or table-valued function name.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT s.name AS statistics_name  
              ,c.name AS column_name  
              ,sc.stats_column_id  
          FROM sys.stats AS s  
          INNER JOIN sys.stats_columns AS sc   
              ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
          INNER JOIN sys.columns AS c   
              ON sc.object_id = c.object_id AND c.column_id = sc.column_id  
          WHERE s.object_id = OBJECT_ID('<schema_name.object_name>');  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
          <a name="_FAQ35"></a> **How do I find the definition of a view?** 
          
          Before you run the following query, replace `<database_name>` and `<schema_name.object_name>` with valid names.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT definition  
          FROM sys.sql_modules  
          WHERE object_id = OBJECT_ID('<schema_name.object_name>');  
          GO  
            
          ```  
            
           Or, you can use the `OBJECT_DEFINITION` function as shown in the following example.  
            
          ```sql  
          USE <database_name>;  
          GO  
          SELECT OBJECT_DEFINITION (OBJECT_ID('<schema_name.object_name>')) AS ObjectDefinition;  
          GO  
            
          ```  
            
           [TOP](#_TOP)  
            
      - question: See Also  
        answer: |
           [Mapping System Tables to System Views (Transact-SQL)](../../relational-databases/system-tables/mapping-system-tables-to-system-views-transact-sql.md)