澳门金沙vip显示 Sql Server 中所有表中的信息

专家解答
通过查询任何数据库中的三个系统表,你可以获得每个表的每一个字段的默认值。下面是这个核心查询。它返回分配给当前数据库中每个用户表的默认值。这个查询在SQL
2000和SQL 2005中都是兼容的。 Quote:复制代码 代码如下:SELECT SO.NAME AS “Table
Name”, SC.NAME AS “Column Name”, SM.TEXT AS “Default Value” FROM
dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id LEFT
JOIN dbo.syscomments SM ON SC.cdefault = SM.id WHERE SO.xtype = ‘U’
ORDER BY SO.[name], SC.colid sysobjects
为我们提供了表元数据。在这个例子中,我们只对表名称感兴趣。syscolumns
表存储与每个表的各个字段相关联的元数据。在这个例子中,我们只需要字段名称。最后,默认值元数据由syscomments表提供。
对Northwind数据库运行这个查询生成下面的结果(为了简短,省略了一些记录)。注意,因为LEFT
JOIN到syscomments表所以它将返回NULL默认值。
现在我在想这个很好的基本查询版本有什么选择。。。 选择1:搜索特别的默认值
通过编辑WHERE条件语句,我们可以在所有的表中查看特别的默认值。 Quote:
SELECT SO.NAME AS “Table Name”, SC.NAME AS “Column Name”, SM.TEXT AS
“Default Value” FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON
SO.id = SC.id LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id WHERE
SO.xtype = ‘U’ AND SM.TEXT = ‘(0)’ ORDER BY SO.[name], SC.colid
选择2:只返回具有默认值字段的信息
修改核心查询的WHERE条件语句来忽略syscomments.text表中的NULL值,这个技巧如下所示:
Quote: 复制代码 代码如下:SELECT SO.NAME
AS “Table Name”, SC.NAME AS “Column Name”, SM.TEXT AS “Default Value”
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id WHERE SO.xtype = ‘U’
AND SM.TEXT IS NOT NULL ORDER BY SO.[name], SC.colid
但是,将FROM条件从句中的JOIN从一个LEFT JOIN改为一个INNER
JOIN会提供优化: Quote: 复制代码
代码如下:SELECT SO.NAME AS “Table Name”, SC.NAME AS “Column Name”,
SM.TEXT AS “Default Value” FROM dbo.sysobjects SO INNER JOIN
dbo.syscolumns SC ON SO.id = SC.id INNER JOIN dbo.syscomments SM ON
SC.cdefault = SM.id WHERE SO.xtype = ‘U’ ORDER BY SO.[name], SC.colid
其实还有另一个选择,利用SQL
2005中的系统目录视图。前面的查询给我提供了这时所需要的信息,并在SQL
2000和SQL
2005中都可以使用,在SQL2000实例中可以挖掘出与这个默认值(实际上是一个默认约束)关联的额外元数据。通过将这个查询特定在系统目录视图上,我们可以获得在之前的查询中没有显示出来的额外信息。
Quote: 复制代码 代码如下:SELECT
ST.[name] AS “Table Name”, SC.[name] AS “Column Name”, SD.definition
AS “Default Value”, SD.[name] AS “Constraint Name” FROM sys.tables ST
INNER JOIN sys.syscolumns SC ON ST.[object_id] = SC.[id] INNER JOIN
sys.default_constraints SD ON ST.[object_id] =
SD.[parent_object_id] AND SC.colid = SD.parent_column_id ORDER BY
ST.[name], SC.colid
所以记住,就因为你被告知没有更好的方法,依靠你作为一个数据库管理员的本能来钻研。你永远不会知道你可能会得到些什么。

复制代码 代码如下:CREATE VIEW
dbo.vw_db_dictionary AS SELECT TOP 100 PERCENT dbo.sysobjects.name AS
table_name, dbo.sysproperties.[value] AS table_desc,
dbo.syscolumns.name AS field, properties.[value] AS field_desc,
dbo.systypes.name AS field_type, dbo.syscolumns.length AS field_size,
ISNULL(COLUMNPROPERTY(dbo.syscolumns.id, dbo.syscolumns.name, ‘Scale’),
0) AS field_precision, dbo.syscolumns.isnullable AS nullable, CASE WHEN
syscomments.text IS NULL THEN ” ELSE syscomments.text END AS
default_value, CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name,
‘IsIdentity’) = 1 THEN ‘√’ ELSE ” END AS is_identity, CASE WHEN EXISTS
(SELECT 1 FROM sysobjects WHERE xtype = ‘PK’ AND name IN (SELECT name
FROM sysindexes WHERE indid IN (SELECT indid FROM sysindexkeys WHERE id
= syscolumns.id AND colid = syscolumns.colid))) THEN ‘√’ ELSE ” END AS
is_key FROM dbo.syscolumns INNER JOIN dbo.sysobjects ON
dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN dbo.systypes ON
dbo.syscolumns.xtype = dbo.systypes.xtype LEFT OUTER JOIN
dbo.sysproperties properties ON dbo.syscolumns.id = properties.id AND
dbo.syscolumns.colid = properties.smallid LEFT OUTER JOIN
dbo.sysproperties ON dbo.sysobjects.id = dbo.sysproperties.id AND
dbo.sysproperties.smallid = 0 LEFT OUTER JOIN dbo.syscomments ON
dbo.syscolumns.cdefault = dbo.syscomments.id WHERE (dbo.sysobjects.xtype
= ‘U’) ORDER BY dbo.sysobjects.name

显示某个Sql Server某个数据库中所有表或视图的信息 sql server 2000 与 2005
不同 差别在于
红色字部分以下语句为获取所有表信息,替换绿色黑体字”U”为”V”为获取所有视图信息。

Sql Server 2000 版本