MsSql查询表信息语句(持续更新Ing...)
代码
/*
说明：查询主外键关系
版本：ALL
说明：查询主外键关系
版本：ALL
编辑：HUGO.CM（mingming0201@vip.qq.com）
摘自：CodeSmith
时间：2011年1月30日 00:11:41
注：大家可以任意转载。如果在本人的语句上做修改的话，也请发到我邮箱一份。非常感谢
*/
SELECT
[fs].[name] AS [ForeignTableName], --外键表名
[fsysusers].[name] AS [ForeignTableOwner], --外键数据库架构名
[rs].[name] AS [PrimaryTableName], --主键表名
[rsysusers].[name] AS [PrimaryTableOwner], --主键数据库架构名
[cs].[name] AS [ConstraintName], --约束名
[fc].[name] AS [ForeignColumnName], --外键字段名
[rc].[name] AS [PrimaryColumnName], --主键字段名
CONVERT(bit, OBJECTPROPERTY([constid], N'CnstIsDisabled')) AS [Disabled], --是否禁用 FOREIGN KEY 约束
CONVERT(bit, OBJECTPROPERTY([constid], N'CnstIsNotRepl')) AS [IsNotForReplication], --是否FOREIGN KEY 约束通过 NOT FOR REPLICATION 选项创建
CONVERT(tinyint, ISNULL(OBJECTPROPERTY([constid], N'CnstIsUpdateCascade'), 0)) AS [UpdateReferentialAction], --执行更新时为此 FOREIGN KEY 声明的引用操作。0：不执行任何操作；1：级联；2：设置空值；3：设置默认值
CONVERT(tinyint, ISNULL(OBJECTPROPERTY([constid], N'CnstIsDeleteCascade'), 0)) AS [DeleteReferentialAction], --执行删除时为此 FOREIGN KEY 声明的引用操作。0：不执行任何操作；1：级联；2：设置 Null；3：设置默认值
CONVERT(bit, OBJECTPROPERTY([constid], N'CnstIsNotTrusted')) AS [WithNoCheck] --是否为系统尚未验证 FOREIGN KEY 约束
FROM [dbo].[sysforeignkeys] WITH (NOLOCK)
INNER JOIN [dbo].[sysobjects] [fs] WITH (NOLOCK) ON [sysforeignkeys].[fkeyid] = [fs].[id]
INNER JOIN [dbo].[sysobjects] [rs] WITH (NOLOCK) ON [sysforeignkeys].[rkeyid] = [rs].[id]
INNER JOIN [dbo].[sysobjects] [cs] WITH (NOLOCK) ON [sysforeignkeys].[constid] = [cs].[id]
LEFT JOIN [dbo].[sysusers] [fsysusers] WITH (NOLOCK) ON [fsysusers].[uid] = [fs].[uid]
LEFT JOIN [dbo].[sysusers] [rsysusers] WITH (NOLOCK) ON [rsysusers].[uid] = [rs].[uid]
INNER JOIN [dbo].[syscolumns] [fc] WITH (NOLOCK) ON [sysforeignkeys].[fkey] = [fc].[colid] AND [sysforeignkeys].[fkeyid] = [fc].[id]
INNER JOIN [dbo].[syscolumns] [rc] WITH (NOLOCK) ON [sysforeignkeys].[rkey] = [rc].[colid] AND [sysforeignkeys].[rkeyid] = [rc].[id]
摘自：CodeSmith
时间：2011年1月30日 00:11:41
注：大家可以任意转载。如果在本人的语句上做修改的话，也请发到我邮箱一份。非常感谢
*/
SELECT
[fs].[name] AS [ForeignTableName], --外键表名
[fsysusers].[name] AS [ForeignTableOwner], --外键数据库架构名
[rs].[name] AS [PrimaryTableName], --主键表名
[rsysusers].[name] AS [PrimaryTableOwner], --主键数据库架构名
[cs].[name] AS [ConstraintName], --约束名
[fc].[name] AS [ForeignColumnName], --外键字段名
[rc].[name] AS [PrimaryColumnName], --主键字段名
CONVERT(bit, OBJECTPROPERTY([constid], N'CnstIsDisabled')) AS [Disabled], --是否禁用 FOREIGN KEY 约束
CONVERT(bit, OBJECTPROPERTY([constid], N'CnstIsNotRepl')) AS [IsNotForReplication], --是否FOREIGN KEY 约束通过 NOT FOR REPLICATION 选项创建
CONVERT(tinyint, ISNULL(OBJECTPROPERTY([constid], N'CnstIsUpdateCascade'), 0)) AS [UpdateReferentialAction], --执行更新时为此 FOREIGN KEY 声明的引用操作。0：不执行任何操作；1：级联；2：设置空值；3：设置默认值
CONVERT(tinyint, ISNULL(OBJECTPROPERTY([constid], N'CnstIsDeleteCascade'), 0)) AS [DeleteReferentialAction], --执行删除时为此 FOREIGN KEY 声明的引用操作。0：不执行任何操作；1：级联；2：设置 Null；3：设置默认值
CONVERT(bit, OBJECTPROPERTY([constid], N'CnstIsNotTrusted')) AS [WithNoCheck] --是否为系统尚未验证 FOREIGN KEY 约束
FROM [dbo].[sysforeignkeys] WITH (NOLOCK)
INNER JOIN [dbo].[sysobjects] [fs] WITH (NOLOCK) ON [sysforeignkeys].[fkeyid] = [fs].[id]
INNER JOIN [dbo].[sysobjects] [rs] WITH (NOLOCK) ON [sysforeignkeys].[rkeyid] = [rs].[id]
INNER JOIN [dbo].[sysobjects] [cs] WITH (NOLOCK) ON [sysforeignkeys].[constid] = [cs].[id]
LEFT JOIN [dbo].[sysusers] [fsysusers] WITH (NOLOCK) ON [fsysusers].[uid] = [fs].[uid]
LEFT JOIN [dbo].[sysusers] [rsysusers] WITH (NOLOCK) ON [rsysusers].[uid] = [rs].[uid]
INNER JOIN [dbo].[syscolumns] [fc] WITH (NOLOCK) ON [sysforeignkeys].[fkey] = [fc].[colid] AND [sysforeignkeys].[fkeyid] = [fc].[id]
INNER JOIN [dbo].[syscolumns] [rc] WITH (NOLOCK) ON [sysforeignkeys].[rkey] = [rc].[colid] AND [sysforeignkeys].[rkeyid] = [rc].[id]
代码
/*
说明：查询字段信息
版本：ALL
编辑：HUGO.CM（mingming0201@vip.qq.com）
摘自：CodeSmith
时间：2010年12月14日 21:05:53
注：大家可以任意转载。如果在本人的语句上做修改的话，也请发到我邮箱一份。非常感谢
*/
SELECT tbl.uid,tbl.[name],
clmns.[name] AS [Name], --字段名
usrt.[name] AS [DataType], --扩展用户类型
ISNULL(baset.[name], N'') AS [SystemType], --数据库类型
CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS int) AS [Length], --长度
CAST(clmns.xprec AS tinyint) AS [NumericPrecision], --精度
CAST(clmns.xscale AS int) AS [NumericScale], --小数位数
CAST(clmns.isnullable AS bit) AS [IsNullable], --是否可为Null
defaults.text AS [DefaultValue], --默认值
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int) AS [Identity], --是否为标识
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsRowGuidCol') AS int) AS IsRowGuid, --是否具有 uniqueidentifier 数据类型(GUID)
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsComputed') AS int) AS IsComputed, --是否为计算列
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsDeterministic') AS int) AS IsDeterministic, --是否为确定性列(此属性只适用于计算列和视图列)
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(tbl.[name]) ELSE 0 END AS nvarchar(40)) AS [IdentitySeed], --种子值
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(tbl.[name]) ELSE 0 END AS nvarchar(40)) AS [IdentityIncrement], --增量值
cdef.[text] AS ComputedDefinition, --SQL 定义语句的实际文本
clmns.[collation] AS Collation, --列的排序规则的名称
CAST(clmns.colid AS int) AS ObjectId, --列Id(顺序)
tbl.[name] AS [TableName] --表名
FROM dbo.sysobjects AS tbl WITH (NOLOCK)
INNER JOIN dbo.syscolumns AS clmns WITH (NOLOCK) ON clmns.id=tbl.id
LEFT JOIN dbo.systypes AS usrt WITH (NOLOCK) ON usrt.xusertype = clmns.xusertype
LEFT JOIN dbo.sysusers AS sclmns WITH (NOLOCK) ON sclmns.uid = usrt.uid
LEFT JOIN dbo.systypes AS baset WITH (NOLOCK) ON baset.xusertype = clmns.xtype and baset.xusertype = baset.xtype
LEFT JOIN dbo.syscomments AS defaults WITH (NOLOCK) ON defaults.id = clmns.cdefault
LEFT JOIN dbo.syscomments AS cdef WITH (NOLOCK) ON cdef.id = clmns.id AND cdef.number = clmns.colid
WHERE (tbl.[type] = 'U')
说明：查询字段信息
版本：ALL
编辑：HUGO.CM（mingming0201@vip.qq.com）
摘自：CodeSmith
时间：2010年12月14日 21:05:53
注：大家可以任意转载。如果在本人的语句上做修改的话，也请发到我邮箱一份。非常感谢
*/
SELECT tbl.uid,tbl.[name],
clmns.[name] AS [Name], --字段名
usrt.[name] AS [DataType], --扩展用户类型
ISNULL(baset.[name], N'') AS [SystemType], --数据库类型
CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS int) AS [Length], --长度
CAST(clmns.xprec AS tinyint) AS [NumericPrecision], --精度
CAST(clmns.xscale AS int) AS [NumericScale], --小数位数
CAST(clmns.isnullable AS bit) AS [IsNullable], --是否可为Null
defaults.text AS [DefaultValue], --默认值
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int) AS [Identity], --是否为标识
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsRowGuidCol') AS int) AS IsRowGuid, --是否具有 uniqueidentifier 数据类型(GUID)
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsComputed') AS int) AS IsComputed, --是否为计算列
CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsDeterministic') AS int) AS IsDeterministic, --是否为确定性列(此属性只适用于计算列和视图列)
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(tbl.[name]) ELSE 0 END AS nvarchar(40)) AS [IdentitySeed], --种子值
CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(tbl.[name]) ELSE 0 END AS nvarchar(40)) AS [IdentityIncrement], --增量值
cdef.[text] AS ComputedDefinition, --SQL 定义语句的实际文本
clmns.[collation] AS Collation, --列的排序规则的名称
CAST(clmns.colid AS int) AS ObjectId, --列Id(顺序)
tbl.[name] AS [TableName] --表名
FROM dbo.sysobjects AS tbl WITH (NOLOCK)
INNER JOIN dbo.syscolumns AS clmns WITH (NOLOCK) ON clmns.id=tbl.id
LEFT JOIN dbo.systypes AS usrt WITH (NOLOCK) ON usrt.xusertype = clmns.xusertype
LEFT JOIN dbo.sysusers AS sclmns WITH (NOLOCK) ON sclmns.uid = usrt.uid
LEFT JOIN dbo.systypes AS baset WITH (NOLOCK) ON baset.xusertype = clmns.xtype and baset.xusertype = baset.xtype
LEFT JOIN dbo.syscomments AS defaults WITH (NOLOCK) ON defaults.id = clmns.cdefault
LEFT JOIN dbo.syscomments AS cdef WITH (NOLOCK) ON cdef.id = clmns.id AND cdef.number = clmns.colid
WHERE (tbl.[type] = 'U')
