SQL Server システム カタログに対するクエリに関してよく寄せられる質問Querying the SQL Server System Catalog FAQ

適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) 適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions)

このトピックには、よく寄せられる質問の一覧が含まれています。This topic contains a list of frequently asked questions. これらの質問に対する答えは、カタログビューに基づくクエリです。The answers to these questions are queries that are based on catalog views.

よく寄せられる質問Frequently Asked Questions

以下のセクションでは、よく寄せられる質問をカテゴリ別に示します。The sections below list frequently asked questions by category.

データ型Data Types

テーブル、インデックス、ビュー、および制約Tables, Indexes, Views, and Constraints

モジュール (ストアド プロシージャ、ユーザー定義関数、およびトリガー)Modules (Stored Procedures, User-Defined Functions, and Triggers)

スキーマ、ユーザー、ロール、およびアクセス許可Schemas, Users, Roles, and Permissions

回答Answers

指定されたデータベースにクラスター化インデックスがないすべてのテーブルを検索操作方法には、How do I find all the tables that do not have a clustered index in a specified database?

次のクエリを実行する前に、を <database_name> 有効なデータベース名に置き換えます。Before you run the following queries, replace <database_name> with a valid database name.

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  
  

または、 OBJECTPROPERTY 次の例に示すように、関数を使用することもできます。Or, you can use the OBJECTPROPERTY function as shown in the following example.

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  
  

TOPTOP

指定したスキーマに含まれるエンティティのすべての所有者を検索操作方法には、How do I find all the owners of entities contained in a specified schema?

次のクエリを実行する前に、 <database_name> とを <schema_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <schema_name> with valid names.

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  
  

TOPTOP

主キーを持たないすべてのテーブルを検索操作方法にはHow do I find all the tables that do not have a primary key?

次のクエリを実行する前に、を <database_name> 有効なデータベース名に置き換えます。Before you run the following queries, replace <database_name> with a valid database name.

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.

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  
  

TOPTOP

インデックスを持たないすべてのテーブルを検索操作方法にはHow do I find all the tables that do not have an index?

次のクエリを実行する前に、を <database_name> 有効なデータベース名に置き換えます。Before you run the following query, replace <database_name> with a valid database name.

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  
  

TOPTOP

Id 列を含むすべてのテーブルを検索操作方法には、How do I find all the tables that have an identity column?

次のクエリを実行する前に、を <database_name> 有効なデータベース名に置き換えます。Before you run the following query, replace <database_name> with a valid database name.

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.

注意

このクエリでは、列の名前は返されません。This query does not return the name of the columns.

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  
  

TOPTOP

指定されたテーブルの列のデータ型を検索操作方法には、How do I find the data types of the columns of a specified table?

次のクエリを実行する前に、 <database_name> とを <schema_name.table_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

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  
  

TOPTOP

指定された関数の依存関係を検索操作方法にはHow do I find the dependencies on a specified function?

次のクエリを実行する前に、 <database_name> とを <schema_name.function_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <schema_name.function_name> with valid names.

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  
  

TOPTOP

データベース内のすべてのストアドプロシージャを検索操作方法には、How do I find all the stored procedures in a database?

次のクエリを実行する前に、を <database_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> with a valid name.

  
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  
  

TOPTOP

指定されたストアドプロシージャまたは関数のパラメーターを検索操作方法にはHow do I find the parameters for a specified stored procedure or function?

次のクエリを実行する前に、 <database_name> とを <schema_name.object_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

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  
  

TOPTOP

データベース内のすべてのユーザー定義関数を検索操作方法にはHow do I find all the user-defined functions in a database?

次のクエリを実行する前に、を <database_name> 有効なデータベース名に置き換えます。Before you run the following query, replace <database_name> with a valid database name.

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  
  

TOPTOP

データベース内のすべてのビューを検索操作方法にはHow do I find all views in a database?

次のクエリを実行する前に、を <database_name> 有効なデータベース名に置き換えます。Before you run the following query, replace <database_name> with a valid database name.

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;  
  

TOPTOP

過去 N 日間に変更されたすべてのエンティティを検索操作方法には、How do I find all the entities that have been modified in the last N days?

次のクエリの <database_name><n_days> を有効な値に置き換えてから、クエリを実行します。Before you run the following query, replace <database_name> and <n_days> with valid values.

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  
  

TOPTOP

指定されたテーブルの LOB データ型を検索操作方法にはHow do I find the LOB data types of a specified table?

次のクエリを実行する前に、 <database_name> とを <schema_name.table_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

  
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  
  

TOPTOP

モジュールの定義を表示操作方法にはHow do I view the definition of a module?

次のクエリを実行する前に、 <database_name> とを <schema_name.object_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;  
GO  
SELECT definition  
FROM sys.sql_modules  
WHERE object_id = OBJECT_ID('<schema_name.object_name>');  
GO  
  

または、 OBJECT_DEFINITION 次の例に示すように、関数を使用することもできます。Or, you can use the OBJECT_DEFINITION function as shown in the following example.

USE <database_name>;  
GO  
SELECT OBJECT_DEFINITION (OBJECT_ID('<schema_name.object_name>')) AS ObjectDefinition;  
GO  
  

TOPTOP

サーバーレベルのトリガーの定義を表示操作方法にはHow do I view the definition of a server-level trigger?

SELECT definition  
FROM sys.server_sql_modules;  
GO  
  

TOPTOP

指定されたテーブルの主キーの列を検索操作方法には、次のようにします。How do I find the columns of a primary key for a specified table?

次のクエリを実行する前に、 <database_name> とを <schema_name.table_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

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  
  

または、 COL_NAME 次の例に示すように、関数を使用することもできます。Or, you can use the COL_NAME function as shown in the following example.

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  
  

TOPTOP

指定されたテーブルの外部キーの列を検索操作方法にはHow do I find the columns of a foreign key for a specified table?

次のクエリを実行する前に、 <database_name> とを <schema_name.table_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

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>');  
  

TOPTOP

指定したプリンシパルに対して許可または拒否されたアクセス許可を検索操作方法には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.

-- 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  

TOPTOP

列が計算列の式で使用されているかどうかを判断操作方法にはHow do I determine if a column is used in a computed column expression?

次のクエリを実行する前に、、、 <database_name> <schema_name.table_name> および <column_name> を有効な名前に置き換えてください。Before you run the following query, replace <database_name>, <schema_name.table_name>, and <column_name> with valid names.

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  
  

TOPTOP

計算列の式で使用されているすべての列を検索操作方法には、How do I find all the columns that are used in a computed column expression?

次のクエリを実行する前に、を <database_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> with a valid name.

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  
  

TOPTOP

指定された CLR ユーザー定義型または別名型に依存する列を検索操作方法にはHow do I find the columns that depend on a specified CLR user-defined type or alias type?

次のクエリを実行する前に、を有効な名前に置き換え、を <database_name> <schema_name.data_type_name> スキーマ修飾の CLR ユーザー定義型またはスキーマ修飾された別名型の名前に置き換えます。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. 次のクエリでは、 db_ownerロールのメンバーシップ、またはデータベース内のすべての依存列および計算列のメタデータを表示する権限が必要です。The following query requires membership in the db_owner role or permissions to see all dependent column and computed column metadata in the database.

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  
  

次のクエリは、CLR ユーザー定義型または別名に依存する列の制限されたビューを返しますが、結果セットはpublicロールで参照できます。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.

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  
  

TOPTOP

指定された CLR ユーザー定義型または別名型に依存する計算列を検索操作方法にはHow do I find the computed columns that depend on a specified CLR user-defined type or alias type?

次のクエリの <database_name> を有効な名前に置き換え、<schema_name.data_type_name> をスキーマ修飾の CLR ユーザー定義型または別名型の有効な名前に置き換えてから、クエリを実行します。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.

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  
  

TOPTOP

指定された CLR ユーザー定義型または別名型に依存するパラメーターを検索操作方法にはHow do I find the parameters that depend on a specified CLR user-defined type or alias type?

次のクエリの <database_name> を有効な名前に置き換え、<schema_name.data_type_name> をスキーマ修飾の CLR ユーザー定義型または別名型の有効な名前に置き換えてから、クエリを実行します。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. 次のクエリでは、 db_ownerロールのメンバーシップ、またはデータベース内のすべての依存列および計算列のメタデータを表示する権限が必要です。The following query requires membership in the db_owner role or permissions to see all dependent column and computed column metadata in the database.

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  
  

次のクエリは、CLR ユーザー定義型または別名に依存するパラメーターの制限されたビューを返しますが、結果セットはpublicロールから参照できます。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.

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  
  

TOPTOP

指定された CLR ユーザー定義型に依存する CHECK 制約を検索操作方法には、How do I find the CHECK constraints that depend on a specified CLR user-defined type?

次のクエリを実行する前に、を有効な名前に置き換え、を <database_name> <schema_name.data_type_name> スキーマ修飾の CLR ユーザー定義型の有効な名前に置き換えます。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.

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  
  

TOPTOP

指定した CLR ユーザー定義型または別名型に依存するビュー、Transact-sql 関数、および Transact-sql ストアドプロシージャを検索操作方法には、以下を参照してください。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?

次のクエリの <database_name> を有効な名前に置き換え、<schema_name.data_type_name> をスキーマ修飾の CLR ユーザー定義型または別名型の有効な名前に置き換えてから、クエリを実行します。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. したがって、CLR ユーザー定義型または別名型に依存するパラメーターは、 sql_dependenciesカタログビューを使用して表示できます。Therefore, parameters that depend on a CLR user-defined type or alias type can be viewed by using the sys.sql_dependencies catalog view. プロシージャとトリガーはスキーマバインドされていません。Procedures and triggers are not schema bound. つまり、プロシージャやトリガーの本体で定義されている式と、CLR ユーザー定義型または別名型の間の依存関係は保守されません。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. CLR ユーザー定義型または別名型に依存する式を持つスキーマバインドビューおよびスキーマバインドユーザー定義関数は、 sql_dependenciesカタログビューで保持されます。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. 型と CLR 関数と CLR プロシージャの間の依存関係は保持されません。Dependencies between types and CLR functions and CLR procedures are not maintained.

次のクエリは、指定された CLR ユーザー定義型または別名型に関して、ビュー、Transact-SQLTransact-SQL 関数、および Transact-SQLTransact-SQL ストアド プロシージャ内でスキーマにバインドされた依存関係をすべて返します。The following query returns all schema-bound dependencies in views, Transact-SQLTransact-SQL functions, and Transact-SQLTransact-SQL stored procedures for a specified CLR user-defined type or alias type.

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  
  

TOPTOP

指定されたテーブルのすべての制約を検索操作方法にはHow do I find all the constraints for a specified table?

次のクエリを実行する前に、 <database_name> とを <schema_name.table_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

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  
  

TOPTOP

指定されたテーブルのすべてのインデックスを検索操作方法にはHow do I find all the indexes for a specified table?

次のクエリを実行する前に、 <database_name> とを <schema_name.table_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

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  
  

TOPTOP

指定された列名を持つすべてのオブジェクトを検索操作方法にはHow do I find all the objects that have a specified column name?

次のクエリを実行する前に、 <database_name> とを <column_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <column_name> with valid names.

USE <database_name>;  
GO  
SELECT OBJECT_NAME(object_id)  
FROM sys.columns  
WHERE name = '<column_name>';  
GO  
  

またはOr

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  
  

TOPTOP

指定されたデータベース内のすべてのユーザー定義テーブルを検索操作方法にはHow do I find all the user-defined tables in a specified database?

次のクエリを実行する前に、を <database_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> with a valid name.

USE <database_name>;  
GO  
SELECT *   
FROM sys.tables;  
GO  
  

TOPTOP

パーティション分割されているすべてのテーブルとインデックスを検索操作方法にはHow do I find all the tables and indexes that are partitioned?

次のクエリを実行する前に、を <database_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> with a valid name.

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  
  

TOPTOP

指定されたオブジェクトのすべての統計を検索操作方法にはHow do I find all the statistics on a specified object?

次のクエリを実行する前に、を有効な名前に置き換え、を <database_name> <schema_name.object_name> 有効なテーブル、インデックス付きビュー、またはテーブル値関数の名前に置き換えます。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.

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  
  

TOPTOP

指定されたオブジェクトのすべての統計列と統計列を検索操作方法にはHow do I find all the statistics and statistics columns on a specified object?

次のクエリを実行する前に、を有効な名前に置き換え、を <database_name> <schema_name.object_name> 有効なテーブル、インデックス付きビュー、またはテーブル値関数の名前に置き換えます。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.

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  
  

TOPTOP

ビューの定義を検索操作方法にはHow do I find the definition of a view?

次のクエリを実行する前に、 <database_name> とを <schema_name.object_name> 有効な名前に置き換えます。Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;  
GO  
SELECT definition  
FROM sys.sql_modules  
WHERE object_id = OBJECT_ID('<schema_name.object_name>');  
GO  
  

または、 OBJECT_DEFINITION 次の例に示すように、関数を使用することもできます。Or, you can use the OBJECT_DEFINITION function as shown in the following example.

USE <database_name>;  
GO  
SELECT OBJECT_DEFINITION (OBJECT_ID('<schema_name.object_name>')) AS ObjectDefinition;  
GO  
  

TOPTOP

関連項目See Also

システムビューへのシステムテーブルのマッピング (Transact-sql)Mapping System Tables to System Views (Transact-SQL)