Contents
- 1 讌 一危磯伎れ 企ろ壱 碁煙り 企 覈 谿場朱る 企至 伎 蟾?
- 2 讌 ろる 壱 襯 覈 谿場朱る 企至 伎 蟾?
- 3 蠍磯蓋 り 企 覈 谿場朱る 企至 伎 蟾?
- 4 碁煙り 企 覈 谿場朱る 企至 伎 蟾?
- 5 ID 伎 企 覈 谿場朱る 企至 伎 蟾?
- 6 讌 企 伎 一危 谿場朱る 企至 伎 蟾?
- 7 讌 譬煙 谿場朱る 企至 伎 蟾?
- 8 一危磯伎れ 襦襯 覈 谿場朱る 企至 伎 蟾?
- 9 讌 襦 襷り 覲襯 谿場朱る 企至 伎 蟾?
- 10 一危磯伎れ 襯 覈 谿場朱る 企至 伎 蟾?
- 11 一危磯伎れ 觀磯ゼ 覈 谿場朱る 企至 伎 蟾?
- 12 豕蠏 n 壱磯ゼ 覈 谿場朱る 企至 伎 蟾?
- 13 讌 企 LOB 一危 谿場朱る 企至 伎 蟾?
- 14 覈 襯 覲企る 企至 伎 蟾?
- 15 覯 譴 碁Μ蟇一 襯 覲企る 企至 伎 蟾?
- 16 讌 企 蠍磯蓋 伎 谿場朱る 企至 伎 蟾?
- 17 讌 企 碁 伎 谿場朱る 企至 伎 蟾?
- 18 讌 覲伎 譯殊牡 蟇磯 蟠 谿場朱る 企至 伎 蟾?
- 19 伎 螻 讌 企至 誤蟾?
- 20 螻 伎 覈 谿場朱る 企至 伎 蟾?
- 21 讌 CLR 覲豺 譬 伎 谿場朱る 企至 伎 蟾?
- 22 讌 CLR 覲豺 譬 螻 伎 谿場朱る 企至 伎 蟾?
- 23 讌 CLR 覲豺 譬 襷り 覲襯 谿場朱る 企至 伎 蟾?
- 24 讌 CLR 譬 CHECK 譟郁唄 谿場朱る 企至 伎 蟾?
- 25 讌 CLR 覲豺 譬 觀, 覦 襦襯 谿場朱る 企至 伎 蟾?
- 26 讌 企 譟郁唄 覈 谿場朱る 企至 伎 蟾?
- 27 讌 企 碁煙るゼ 覈 谿場朱る 企至 伎 蟾?
- 28 讌 企 螳豌企ゼ 覈 谿場朱る 企至 伎 蟾?
- 29 讌 一危磯伎れ 企 覈 谿場朱る 企至 伎 蟾?
- 30 覿 企 覦 碁煙るゼ 覈 谿場朱る 企至 伎 蟾?
- 31 讌 螳豌伎 糾襯 覈 谿場朱る 企至 伎 蟾?
- 32 讌 螳豌伎 糾 覦 糾 伎 覈 谿場朱る 企至 伎 蟾?
- 33 觀一 襯 谿場朱る 企至 伎 蟾?
- 34 谿瑚襭
SQL Server 2008 R2
1 讌 一危磯伎れ 企ろ壱 碁煙り 企 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
2 讌 ろる 壱 襯 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
3 蠍磯蓋 り 企 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
4 碁煙り 企 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
5 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
6 讌 企 伎 一危 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
7 讌 譬煙 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
8 一危磯伎れ 襦襯 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
9 讌 襦 襷り 覲襯 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
10 一危磯伎れ 襯 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
11 一危磯伎れ 觀磯ゼ 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
12 豕蠏 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
13 讌 企 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
14 覈 襯 覲企る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
15 覯 譴 碁Μ蟇一 襯 覲企る 企至 伎 蟾? #
SELECT definition
FROM sys.server_sql_modules;
GO
16 讌 企 蠍磯蓋 伎 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
17 讌 企 碁 伎 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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>');
18 讌 覲伎 譯殊牡 蟇磯 蟠 谿場朱る 企至 伎 蟾? #
れ 蟠 碁 壱一 企 覦 襯 襷. 結覿 る 貎朱Μ 語. 蟠 誤 覈 一危磯伎れ 襯 襷れ伎 .
-- 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
19 伎 螻 讌 企至 誤蟾? #
れ 貎朱Μ襯 ろ蠍 <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
20 螻 伎 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
21 讌 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
22 讌 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
23 讌 CLR 覲豺 譬 襷り 覲襯 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <database_name> 覦襯 企朱 豌危螻 <schema_name.data_type_name> 覦襯 ろる CLR , 覲豺 企朱 豌危. れ 貎朱Μ 一危磯伎れ 覈 譬 覦 螻 覃一危磯ゼ 覲願鍵 db_owner 覃る 蟆 蟠 .
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
24 讌 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
25 讌 CLR 覲豺 譬 觀, 覦 襦襯 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <database_name> 覦襯 企朱 豌危螻 <schema_name.data_type_name> 覦襯 ろる CLR , 覲豺 企朱 豌危.
襦 襷り 覲 朱 ろる 覦碁. 磯殊 CLR 覲豺 譬 襷り 覲 sys.sql_dependencies 豺危襦蠏 觀磯ゼ 覲 給. 襦 覦 碁Μ蟇磯 ろる 覦碁讌 給. 讀, 襦 碁Μ蟇 覲碁語 覈 螻 CLR 覲豺 螳 譬煙 讌讌 給. CLR 覲豺 譬 ろる 覦企 觀 覦 ろる 覦企 sys.sql_dependencies 豺危襦蠏 觀一 讌. , CLR 覦 CLR 襦 螳 譬煙 讌讌 給.
れ 貎朱Μ 讌 CLR 覲豺 觀, 覦 襦 覈 ろる 覦企 譬煙 覦.
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
26 讌 企 譟郁唄 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
27 讌 企 碁煙るゼ 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
28 讌 企 螳豌企ゼ 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
29 讌 一危磯伎れ 企 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <database_name> 覦襯 企朱 豌危.
USE <database_name>;
GO
SELECT *
FROM sys.tables;
GO
30 覿 企 覦 碁煙るゼ 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
31 讌 螳豌伎 糾襯 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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
32 讌 螳豌伎 糾 覦 糾 伎 覈 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <database_name> 覦襯 企朱 豌危螻 <schema_name.object_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
33 觀一 襯 谿場朱る 企至 伎 蟾? #
れ 貎朱Μ襯 ろ蠍 <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