[アーティクル]
10/31/2012

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

このトピックでは、よく寄せられる質問の一覧を掲載しています。これらの質問に対する答えは、カタログビューに基づくクエリです。

よく寄せられる質問

以下のセクションでは、よく寄せられる質問をカテゴリ別に示しています。

データ型

指定されたテーブルの各列のデータ型を知るにはどのようにすればよいですか。
指定されたテーブルの LOB データ型を見つけるにはどのようにすればよいですか。
指定されたデータ型に依存する列を見つけるにはどのようにすればよいですか。
指定された CLR ユーザー定義型または別名データ型に依存する計算列を見つけるにはどのようにすればよいですか。
指定された CLR ユーザー定義型または別名型に依存するパラメータを見つけるにはどのようにすればよいですか。
指定された CLR ユーザー定義型に依存する CHECK 制約を見つけるにはどのようにすればよいですか。
指定された CLR ユーザー定義型または別名データ型に依存するビュー、Transact-SQL 関数、Transact-SQL ストアドプロシージャを見つけるにはどのようにすればよいですか。

テーブル、インデックス、ビュー、および制約

指定されたデータベースでユーザー定義型のテーブルをすべて見つけるにはどのようにすればよいですか。
指定したデータベース中でクラスタ化インデックスを持たないテーブルをすべて見つけるにはどのようにすればよいですか。
インデックスを持たないすべてのテーブルを見つけるにはどのようにすればよいですか。
主キーを持たないすべてのテーブルを見つけるにはどのようにすればよいですか。
ID 列を持つすべてのテーブルを見つけるにはどのようにすればよいですか。
パーティション分割されたテーブルとインデックスをすべて見つけるにはどのようにすればよいですか。
データベース内のすべてのビューを見つけるにはどのようにすればよいですか。
ビューの定義を見つけるにはどのようにすればよいですか。
過去 n 日間に変更されたすべてのエンティティを見つけるにはどのようにすればよいですか。
指定されたテーブルの主キーの列を見つけるにはどのようにすればよいですか。
指定されたテーブルの外部キーの列を見つけるにはどのようにすればよいですか。
計算列の式で使われている列を判断するにはどのようにすればよいですか。
計算列の式で使われている列をすべて見つけるにはどのようにすればよいですか。
指定されたテーブルの制約をすべて見つけるにはどのようにすればよいですか。
指定されたテーブルのインデックスをすべて見つけるにはどのようにすればよいですか。
指定された名前の列を含むすべてのテーブルを見つけるにはどのようにすればよいですか。
指定されたオブジェクトの統計情報をすべて見つけるにはどのようにすればよいですか。
指定されたオブジェクトの統計情報と統計列をすべて見つけるにはどのようにすればよいですか。

モジュール (ストアドプロシージャ、ユーザー定義関数、およびトリガ)

データベース内のすべてのストアドプロシージャを見つけるにはどのようにすればよいですか。
データベース内のすべてのユーザー定義関数を見つけるにはどのようにすればよいですか。
指定されたストアドプロシージャまたは関数に対するパラメータを見つけるにはどのようにすればよいですか。
指定した関数に対する依存関係を知るにはどのようにすればよいですか。
モジュールの定義を表示するにはどのようにすればよいですか。
サーバーレベルのトリガの定義を表示するにはどのようにすればよいですか。

スキーマ、ユーザー、ロール、および権限

指定されたスキーマに含まれるエンティティの所有者をすべて見つけるにはどのようにすればよいですか。
指定されたプリンシパルに付与または定義された権限を見つけるにはどのようにすればよいですか。

回答

指定したデータベース中でクラスタ化インデックスを持たないテーブルをすべて見つけるにはどのようにすればよいですか。

次のクエリの <database_name> を有効なデータベース名に置き換えてから、クエリを実行します。

USE AdventureWorks2008;
GO
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 関数を使用します。

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

トップに戻る

指定されたスキーマに含まれるエンティティの所有者をすべて見つけるにはどのようにすればよいですか。

次のクエリの <database_name> と <schema_name> を有効な名前に置き換えてから、クエリを実行します。

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

トップに戻る

主キーを持たないすべてのテーブルを見つけるにはどのようにすればよいですか。

次のクエリの <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

または次のクエリを実行します。

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

トップに戻る

インデックスを持たないすべてのテーブルを見つけるにはどのようにすればよいですか。

次のクエリの <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

トップに戻る

ID 列を持つすべてのテーブルを見つけるにはどのようにすればよいですか。

次のクエリの <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

または次のクエリを実行します。

注
このクエリは列の名前を返しません。

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

トップに戻る

指定されたテーブルの各列のデータ型を知るにはどのようにすればよいですか。

次のクエリの <database_name> と <schema_name.table_name> を有効な名前に置き換えてから、クエリを実行します。

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

トップに戻る

指定した関数に対する依存関係を知るにはどのようにすればよいですか。

次のクエリの <database_name> と <schema_name.function_name> を有効な名前に置き換えてから、クエリを実行します。

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

トップに戻る

データベース内のすべてのストアドプロシージャを見つけるにはどのようにすればよいですか。

次のクエリの <database_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

トップに戻る

指定されたストアドプロシージャまたは関数に対するパラメータを見つけるにはどのようにすればよいですか。

次のクエリの <database_name> と <schema_name.object_name> を有効な名前に置き換えてから、クエリを実行します。

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

トップに戻る

データベース内のすべてのユーザー定義関数を見つけるにはどのようにすればよいですか。

次のクエリの <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

トップに戻る

データベース内のすべてのビューを見つけるにはどのようにすればよいですか。

次のクエリの <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;
GO

トップに戻る

過去 n 日間に変更されたすべてのエンティティを見つけるにはどのようにすればよいですか。

次のクエリの <database_name> と <n_days> を有効な値に置き換えてから、クエリを実行します。

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

トップに戻る

指定されたテーブルの LOB データ型を見つけるにはどのようにすればよいですか。

次のクエリの <database_name> と <schema_name.table_name> を有効な名前に置き換えてから、クエリを実行します。

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

トップに戻る

モジュールの定義を表示するにはどのようにすればよいですか。

次のクエリの <database_name> と <schema_name.object_name> を有効な名前に置き換えてから、クエリを実行します。

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

または、次の例に示すように、OBJECT_DEFINITION 関数を使用します。

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

トップに戻る

サーバーレベルのトリガの定義を表示するにはどのようにすればよいですか。

SELECT definition
FROM sys.server_sql_modules;
GO

トップに戻る

指定されたテーブルの主キーの列を見つけるにはどのようにすればよいですか。

次のクエリの <database_name> と <schema_name.table_name> を有効な名前に置き換えてから、クエリを実行します。

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 関数を使用します。

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

トップに戻る

指定されたテーブルの外部キーの列を見つけるにはどのようにすればよいですか。

次のクエリの <database_name> と <schema_name.table_name> を有効な名前に置き換えてから、クエリを実行します。

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

トップに戻る

指定されたプリンシパルに付与または定義された権限を見つけるにはどのようにすればよいですか。

次の例では、権限が確認されたエンティティの名前を返す関数を作成します。作成された関数は、後続のクエリで呼び出されます。この関数は権限を確認する各データベースに作成する必要があります。

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

トップに戻る

計算列の式で使われている列を判断するにはどのようにすればよいですか。

次のクエリの <database_name>、<schema_name.table_name>、および <column_name> を有効な名前に置き換えてから、クエリを実行します。

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

トップに戻る

計算列の式で使われている列をすべて見つけるにはどのようにすればよいですか。

次のクエリの <database_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

トップに戻る

指定された CLR ユーザー定義型または別名型に依存する列を見つけるにはどのようにすればよいですか。

次のクエリの <database_name> を有効な名前に置き換え、<schema_name.data_type_name> をスキーマ修飾の CLR ユーザー定義型または別名型の有効な名前に置き換えてから、クエリを実行します。次のクエリを実行するには、db_owner ロールのメンバシップ、またはデータベース内のすべての依存列および計算列メタデータを参照するための権限が必要です。

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 ロールでも参照可能です。ユーザー型の REFERENCE 権限を他のユーザーに付与できる場合は、他のユーザーがその型を使用して作成したオブジェクトのメタデータを参照できる権限がなくても、このクエリを使用できます。

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

トップに戻る

指定された CLR ユーザー定義型または別名型に依存する計算列を見つけるにはどのようにすればよいですか。

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

トップに戻る

指定された CLR ユーザー定義型または別名型に依存するパラメータを見つけるにはどのようにすればよいですか。

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 ロールでも参照可能です。ユーザー型の REFERENCE 権限を他のユーザーに付与できる場合は、他のユーザーがその型を使用して作成したオブジェクトのメタデータを参照できる権限がなくても、このクエリを使用できます。

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

トップに戻る

指定された CLR ユーザー定義型に依存する CHECK 制約を見つけるにはどのようにすればよいですか。

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

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

トップに戻る

指定された CLR ユーザー定義型または別名データ型に依存するビュー、Transact-SQL 関数、Transact-SQL ストアドプロシージャを見つけるにはどのようにすればよいですか。

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

関数やプロシージャ内で定義されたパラメータは、暗黙のうちにスキーマにバインドされます。したがって、CLR ユーザー定義型または別名型に依存するパラメータは sys.sql_dependencies カタログビューを使用して表示できます。プロシージャやトリガはスキーマにバインドされません。つまり、プロシージャやトリガの本体で定義されている式と、CLR ユーザー定義型または別名型の間の依存関係は保守されません。CLR ユーザー定義型または別名型に依存する式を含むスキーマにバインドされたビューとスキーマにバインドされたユーザー定義関数は、sys.sql_dependencies カタログビューで管理されます。型と CLR 関数や CLR プロシージャとの間の依存関係は保守されません。

次のクエリは、指定された CLR ユーザー定義型または別名型に関して、ビュー、Transact-SQL 関数、および Transact-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

トップに戻る

指定されたテーブルの制約をすべて見つけるにはどのようにすればよいですか。

次のクエリの <database_name> と <schema_name.table_name> を有効な名前に置き換えてから、クエリを実行します。

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

トップに戻る

指定されたテーブルのインデックスをすべて見つけるにはどのようにすればよいですか。

次のクエリの <database_name> と <schema_name.table_name> を有効な名前に置き換えてから、クエリを実行します。

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

トップに戻る

指定された名前の列を含むすべてのオブジェクトを見つけるにはどのようにすればよいですか。

次のクエリの <database_name> と <column_name> を有効な名前に置き換えてから、クエリを実行します。

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

または、

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

トップに戻る

指定されたデータベースでユーザー定義型のテーブルをすべて見つけるにはどのようにすればよいですか。

次のクエリの <database_name> を有効な名前に置き換えてから、クエリを実行します。

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

トップに戻る

パーティション分割されたテーブルとインデックスをすべて見つけるにはどのようにすればよいですか。

次のクエリの <database_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

トップに戻る

指定されたオブジェクトの統計情報をすべて見つけるにはどのようにすればよいですか。

次のクエリの <database_name> を有効な名前に置き換え、<schema_name.object_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

トップに戻る

指定されたオブジェクトの統計情報と統計列をすべて見つけるにはどのようにすればよいですか。

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

トップに戻る

ビューの定義を見つけるにはどのようにすればよいですか。

次のクエリの <database_name> と <schema_name.object_name> を有効な名前に置き換えてから、クエリを実行します。

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

または、次の例に示すように、OBJECT_DEFINITION 関数を使用します。

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

トップに戻る

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

よく寄せられる質問

データ型

テーブル、インデックス、ビュー、および制約

モジュール (ストアド プロシージャ、ユーザー定義関数、およびトリガ)

スキーマ、ユーザー、ロール、および権限

回答

指定したデータベース中でクラスタ化インデックスを持たないテーブルをすべて見つけるにはどのようにすればよいですか。

指定されたスキーマに含まれるエンティティの所有者をすべて見つけるにはどのようにすればよいですか。

主キーを持たないすべてのテーブルを見つけるにはどのようにすればよいですか。

インデックスを持たないすべてのテーブルを見つけるにはどのようにすればよいですか。

ID 列を持つすべてのテーブルを見つけるにはどのようにすればよいですか。

指定されたテーブルの各列のデータ型を知るにはどのようにすればよいですか。

指定した関数に対する依存関係を知るにはどのようにすればよいですか。

データベース内のすべてのストアド プロシージャを見つけるにはどのようにすればよいですか。

指定されたストアド プロシージャまたは関数に対するパラメータを見つけるにはどのようにすればよいですか。

データベース内のすべてのユーザー定義関数を見つけるにはどのようにすればよいですか。

データベース内のすべてのビューを見つけるにはどのようにすればよいですか。

過去 n 日間に変更されたすべてのエンティティを見つけるにはどのようにすればよいですか。

指定されたテーブルの LOB データ型を見つけるにはどのようにすればよいですか。

モジュールの定義を表示するにはどのようにすればよいですか。

サーバー レベルのトリガの定義を表示するにはどのようにすればよいですか。

指定されたテーブルの主キーの列を見つけるにはどのようにすればよいですか。

指定されたテーブルの外部キーの列を見つけるにはどのようにすればよいですか。

指定されたプリンシパルに付与または定義された権限を見つけるにはどのようにすればよいですか。

計算列の式で使われている列を判断するにはどのようにすればよいですか。

計算列の式で使われている列をすべて見つけるにはどのようにすればよいですか。

指定された CLR ユーザー定義型または別名型に依存する列を見つけるにはどのようにすればよいですか。

指定された CLR ユーザー定義型または別名型に依存する計算列を見つけるにはどのようにすればよいですか。

指定された CLR ユーザー定義型または別名型に依存するパラメータを見つけるにはどのようにすればよいですか。

指定された CLR ユーザー定義型に依存する CHECK 制約を見つけるにはどのようにすればよいですか。

指定された CLR ユーザー定義型または別名データ型に依存するビュー、Transact-SQL 関数、Transact-SQL ストアド プロシージャを見つけるにはどのようにすればよいですか。

指定されたテーブルの制約をすべて見つけるにはどのようにすればよいですか。

指定されたテーブルのインデックスをすべて見つけるにはどのようにすればよいですか。

指定された名前の列を含むすべてのオブジェクトを見つけるにはどのようにすればよいですか。

指定されたデータベースでユーザー定義型のテーブルをすべて見つけるにはどのようにすればよいですか。

パーティション分割されたテーブルとインデックスをすべて見つけるにはどのようにすればよいですか。

指定されたオブジェクトの統計情報をすべて見つけるにはどのようにすればよいですか。

指定されたオブジェクトの統計情報と統計列をすべて見つけるにはどのようにすればよいですか。

ビューの定義を見つけるにはどのようにすればよいですか。

関連項目

タスク

参照

概念

その他のリソース

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

モジュール (ストアドプロシージャ、ユーザー定義関数、およびトリガ)

データベース内のすべてのストアドプロシージャを見つけるにはどのようにすればよいですか。

指定されたストアドプロシージャまたは関数に対するパラメータを見つけるにはどのようにすればよいですか。

サーバーレベルのトリガの定義を表示するにはどのようにすればよいですか。

指定された CLR ユーザー定義型または別名データ型に依存するビュー、Transact-SQL 関数、Transact-SQL ストアドプロシージャを見つけるにはどのようにすればよいですか。