【转】MSSQL中数据库对象类型解释--觉得很好，自己存档

 

出处：http://blog.ueren.com/?tag=sysobjects


public string GetObjectTypeName(object oType)
{
switch (oType+"")
{
case "U":
return "用户表";
case "V":
return "视图";
case "P":
return "存储过程";
case "TF":
return "表函数";
case "FN":
return "标量函数";
case "C":
return "CHECK约束";
case "D":
return "默认值或DEFAULT约束 ";
case "F":
return "FOREIGN KEY 约束 ";
case "IF":
return "内嵌表函数 ";
case "K":
return "PRIMARY KEY或 UNIQUE约束";
case "L":
return "日志";
case "R":
return "规则";
case "RF":
return "复制筛选存储过程";
case "S":
return "系统表";
case "TR":
return "触发器";
case "X":
return "扩展存储过程";
}
return "";
}


查询数据库脚本：


SELECT name AS DbName,[dbid] As DbId,filename As FileName FROM [Master].dbo.[sysdatabases] Where dbid>4 Order by name


查询表脚本：


SELECT [{0}].dbo.sysobjects.name As TableName,id As TableId,
(Select top 1 rowcnt From [{0}].dbo.sysindexes Where id=[{0}].dbo.sysobjects.id) As RecordCount,
ISNULL(E.name,'') As PropertyName,
ISNULL(E.value,'') As PropertyValue
FROM [{0}].dbo.sysobjects
LEFT JOIN sys.extended_properties E ON E.major_id=[{0}].dbo.sysobjects.id AND E.minor_id=0
Where xtype='U' AND [{0}].dbo.sysobjects.name not in ('dtproperties','sysdiagrams') Order By [{0}].dbo.sysobjects.name


查询数据库对象脚本：


SELECT T.name As ObjectName,T.id As ObjectId,rTRIM(T.xtype) AS ObjectType,
(SELECT Top 1 A.[definition] FROM [{0}].sys.sql_modules A JOIN [{0}].sys.objects B ON A.object_id = B.object_id
Where B.name=T.name) As ObjectDefinition
FROM [{0}].dbo.sysobjects T
Where xtype in ( N'P', N'PC',N'FN', N'IF', N'TF', N'FS', N'FT', N'V',N'TR')
AND T.name not in ('dtproperties','sysdiagrams')
Order By T.name


查询列与字段：


Select
C.name As TableName,
C.id As TableObjectId,
A.name As ColumnName,
A.colid As ColumnObjectId,
A.[length] As ColumnLength,
B.name As ColumnDataType,
ISNULL(D.[text],'') AS DefaultValue,
ISNULL(E.name,'') As PropertyName,
ISNULL(E.value,'') As PropertyValue,
COLUMNPROPERTY(A.id,A.name, 'AllowsNULL') AS AllowsNull,
COLUMNPROPERTY(A.id,A.name, 'IsCursorType') AS IsCursorType,
COLUMNPROPERTY(A.id,A.name, 'IsIdentity') AS IsIdentity,
(Select Count(1) From
sysobjects
INNER JOIN sysindexes ON sysobjects.parent_obj=sysindexes.id AND sysobjects.name=sysindexes.name
INNER JOIN sysindexkeys ON sysindexes.id=sysindexkeys.id AND sysindexes.indid=sysindexkeys.indid
Where sysobjects.xtype='PK' AND sysobjects.parent_obj=A.id AND sysindexkeys.colid=A.colid) AS IsPrimaryKey
From
dbo.syscolumns A
INNER JOIN dbo.systypes B ON B.xusertype=A.xtype
INNER JOIN dbo.sysobjects C ON C.id=A.id AND C.xtype IN ('U','V') AND C.name not in ('dtproperties','sysdiagrams')
LEFT JOIN dbo.syscomments D ON A.cdefault = D.id
LEFT JOIN sys.extended_properties E ON E.major_id=A.id AND E.minor_id=A.colid order by A.colid


查询约束脚本：


SELECT
b.rkeyid AS MasterTableObjectId,
object_name(b.rkeyid) AS MasterTableName,
b.rkey As MasterColumnObjectId,
(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid) AS MasterColumnName,
b.fkeyid AS ForeignTableObjectId,
object_name(b.fkeyid) AS ForeignTableName,
b.fkey AS ForeignColumnObjectId,
(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid) As ForeignColumnName,
ObjectProperty(a.id,'CnstIsUpdateCascade') AS UpdateCascade,
ObjectProperty(a.id,'CnstIsDeleteCascade') AS DeleteCascade
FROM sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
WHERE a.xtype='F' AND c.xtype='U'