master中的系统目录与用户数据库中的区别

每个数据库中的系统目录中都存放了其包含对象的信息。而master中额外存放了此磁盘空间，系统层次的配置，文件的使用和分配，登录信息等。

注意：各数据库中的系统目录包含其自身对象的信息和master中绝大部分系统目录信息。

系统目录的分类：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/catalog-views-transact-sql?view=sql-server-2017

系统目录中的系统表自sql2005后不再能直接访问，可以通过视图和函数等方式访问。下图列出了可访问的途径：

实用的系统目录查询：

/* The Tables */
--数据库中的所有用户表
SELECT
ob.name AS User_Table, Coalesce(ep.value, '') AS documentation
FROM sys.objects AS ob
LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = ob.object_id
AND ep.class = 1
AND ep.minor_id = 0
WHERE ObjectProperty(ob.object_id, 'IsUserTable') = 1
/* The Views */
--视图
SELECT ob.name AS ViewName, Coalesce(ep.value, '') AS documentation
FROM sys.objects ob LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = ob.object_id
AND ep.class = 1
AND ep.minor_id = 0
WHERE objectproperty(ob.object_id,'IsView')= 1

/* The Check Constraints */
--Check约束
SELECT
objects.name AS Name_of_Check_Constraint,
Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
Coalesce(ep.value,'') AS documentation
FROM sys.objects
LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = objects.object_id AND ep.class=1
AND ep.name='MS_Description'--microsoft 公约
WHERE ObjectProperty(objects.object_id, 'IsCheckCnst') = 1

/* The Constraints */

SELECT
--约束
objects.name AS Name_of_Constraint, --see all constraints and parent table
Lower(Replace(type_desc,'_',' ')),--the type of constraint
Object_Schema_Name(objects.parent_object_id) + '.' + Object_Name(objects.parent_object_id) AS parent,
Coalesce(ep.value, '') AS documentation
FROM sys.objects
LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = objects.object_id
AND ep.class = 1
AND ep.name = 'MS_Description'
WHERE ObjectProperty(objects.object_id, 'IsConstraint') = 1;

/* The Defaults */
--默认
SELECT
objects.name,
Coalesce(ep.value, '') AS documentation
FROM sys.objects
LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = objects.object_id
AND ep.class = 1
AND ep.name = 'MS_Description'
WHERE ObjectProperty(objects.object_id, 'IsDefault') = 1;

/* The Default Constraints */
--数据库及其父表中的所有默认约束
SELECT objects.name AS Name_of_Default_Constraint,--see all Default constraints and parent table
Coalesce(ep.value,'') AS documentation,
object_schema_name(objects.parent_object_id)+'.'+object_name(objects.parent_object_id) AS parent,
Coalesce(EP_parent.value,'') AS documentation
FROM sys.objects
LEFT OUTER JOIN sys.extended_properties AS ep
ON ep.major_id = objects.object_id
AND ep.class = 1
AND ep.name = 'MS_Description' --the microsoft convention
LEFT OUTER JOIN sys.extended_properties AS EP_parent
ON ep.major_id = objects.parent_object_id
AND ep.name = 'MS_Description' --the microsoft convention
WHERE objectproperty(objects.object_id,'IsDefaultCnst')= 1;

/* The Executables */
--数据库中的所有可执行文件(过程、函数等)
SELECT
oe.name AS Name_Of_Executable,
Replace(Lower(oe.type_desc), '_', ' ') AS Type_Of_Executable,
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oe
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = oe.object_id
AND EP.name = 'MS_Description'
WHERE ObjectProperty(oe.object_id, 'IsExecuted') = 1;

/* The Extended Stored Procedures */
--数据库中的所有扩展存储过程
SELECT
oep.name AS Name_of_Extended_Procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS oep
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = oep.object_id
AND EP.name = 'MS_Description'
WHERE ObjectProperty(oep.object_id, 'IsExtendedProc') = 1;

/* The Inline Functions */
--数据库中的所有内联函数
SELECT ilf.name AS Inline_function,
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ilf
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = ilf.object_id
AND EP.name = 'MS_Description'
WHERE objectproperty(ilf.object_id,'IsInlineFunction')= 1;

/* The Primary Keys */
--数据库中的所有主键及其父表
SELECT
pk.name AS Primary_key,
Object_Schema_Name(pk.parent_object_id) + '.' + Object_Name(pk.parent_object_id) AS parent,
Coalesce(EP.value, '') AS KeyDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS pk
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = pk.object_id
AND EP.name = 'MS_Description'
LEFT OUTER JOIN sys.extended_properties AS EPParent
ON EPParent.major_id = pk.parent_object_id
AND EPParent.minor_id = 0
AND EPParent.name = 'MS_Description'
WHERE ObjectProperty(pk.object_id, 'IsPrimaryKey') = 1;

/* The Stored Procedures */
--数据库中的所有存储过程
SELECT
sp.name AS Stored_procedure, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sp
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = sp.object_id
AND EP.minor_id = 0
AND EP.name = 'MS_Description'
WHERE ObjectProperty(sp.object_id, 'IsProcedure') = 1;

/* The Queues */
--数据库中的所有队列
SELECT
q.name AS QueueName, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS q
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = q.object_id
AND EP.name = 'MS_Description'
WHERE ObjectProperty(q.object_id, 'IsQueue') = 1;

/* The Rules */
--数据库中的所有旧式规则
SELECT
ru.name AS RuleName, --old-fashioned sybase-style rule
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS ru
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = ru.object_id
AND EP.name = 'MS_Description'
WHERE ObjectProperty(ru.object_id, 'IsRule') = 1;

/* The Scalar Functions */
--数据库中的所有标量函数。
SELECT
sf.name AS Scalar_function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS sf
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = sf.object_id
AND EP.name = 'MS_Description'
WHERE ObjectProperty(sf.object_id, 'IsScalarFunction') = 1;

/* The System Tables */
--据库中的所有系统表
SELECT
st.name AS System_table, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS st
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = st.object_id
AND EP.name = 'MS_Description'
WHERE ObjectProperty(st.object_id, 'IsSystemTable') = 1;

--数据库中的所有表，包括系统表
SELECT
at.name AS TableName,
Lower(Replace(type_desc,'_',' ')),--约束的类型
Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS at
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = at.object_id
AND EP.name = 'MS_Description'
WHERE ObjectProperty(at.object_id, 'IsTable') = 1;

/* The TVFs*/
--数据库中的所有表值函数
SELECT
tvf.name AS Table_Valued_Function, Coalesce(EP.value, '') AS Documentation
FROM sys.objects AS tvf
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = tvf.object_id
AND EP.name = 'MS_Description' --the microsoft convention
WHERE ObjectProperty(tvf.object_id, 'IsTableFunction') = 1;

--数据库及其所有触发器。
SELECT
tr.name AS TriggerName,
Object_Schema_Name(tr.parent_object_id) + '.' + Object_Name(tr.parent_object_id) AS parent,
Coalesce(EP.value, '') AS TriggerDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS tr
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = tr.object_id
AND EP.name = 'MS_Description'
LEFT OUTER JOIN sys.extended_properties AS EPParent
ON EPParent.major_id = tr.parent_object_id
AND EPParent.minor_id = 0
AND EPParent.name = 'MS_Description'
WHERE ObjectProperty(tr.object_id, 'IsTrigger') = 1;

/* The Unique Constraints */
--数据库及其父表中的所有惟一约束
SELECT uc.name AS Unique_constraint,--所有唯一的约束
object_schema_name(uc.parent_object_id)+'.'+object_name(uc.parent_object_id) AS parent,
Coalesce(EP.value, '') AS ConstraintDoc, Coalesce(EPParent.value, '') AS TableDoc
FROM sys.objects AS uc
LEFT OUTER JOIN sys.extended_properties AS EP
ON EP.major_id = uc.object_id
AND EP.name = 'MS_Description'
LEFT OUTER JOIN sys.extended_properties AS EPParent
ON EPParent.major_id = uc.parent_object_id
AND EPParent.minor_id = 0
AND EPParent.name = 'MS_Description'
WHERE objectproperty(uc.object_id,'IsUniqueCnst')= 1;

 

转载于:https://www.cnblogs.com/sxf2086he/p/9298412.html