求几个问题的答案
小弟最近在做一个项目,其中涉及到两处技术实现,之前没有做过。
分别是:(该案中使用的数据库是sqlserver2005)
A)如何通过数据查询得到数据库中表对象,或试图对象的清单
B)如何在已知表对象名的前提下,查询得到该表的表结构清单
[解决办法]
A查询系统试图
比如查询用户表
SELECT NAME FROM SYS.TABLES
视图
SELECT * FROM sys.views
B也是查询系统视图
SYS.COLUMNS,SYS.TABLES,SYS.types
等联结查询就可以,具体系统试图看联机帮助
[解决办法]
1.获取所有数据库名:
SELECT Name FROM Master..SysDatabases ORDER BY Name
2.获取所有表名:
SELECT Name FROM DatabaseName..SysObjects Where XType='U' ORDER BY Name
XType='U':表示所有用户表;
XType='S':表示所有系统表;
3.获取所有字段名:
SELECT Name FROM SysColumns WHERE id=Object_Id('TableName')
[解决办法]
--查询用户表对象信息SELECT Tab.Name AS [表名], Tab.create_date AS [创建时间], Tab.modify_date AS [最后修改时间], Col.Name AS [列名], Type.name AS [数据类型], Col.max_length AS [字段长度], CASE WHEN pk.is_primary_key = 1 THEN 'Y' ELSE 'N' END AS [是否主键], CASE WHEN Col.is_identity = 1 THEN 'Y' ELSE 'N' END AS [是否自增], identity_columns.seed_value AS [自增种子], identity_columns.increment_value AS [自增步长], CASE WHEN Col.is_nullable = 1 THEN 'Y' ELSE 'N' END AS [是否允许为NULL], Def.text AS [默认值], CASE WHEN Col.is_computed = 1 THEN 'Y' ELSE 'N' END AS [是否计算列], computed_columns.definition AS [计算公式], Col_Desc.Value AS [列备注]FROM sys.objects Tab INNER JOIN sys.columns Col ON Tab.object_id = Col.object_id INNER JOIN sys.types Type ON Col.system_type_id = Type.system_type_id LEFT JOIN sys.identity_columns identity_columns ON Tab.object_id = identity_columns.object_id AND Col.column_id = identity_columns.column_id LEFT JOIN syscomments Def ON Col.default_object_id = Def.ID LEFT JOIN(SELECT index_columns.object_id, index_columns.column_id, indexes.is_primary_key FROM sys.indexes indexes INNER JOIN sys.index_columns index_columns ON indexes.object_id = index_columns.object_id AND indexes.index_id = index_columns.index_id WHERE indexes.is_primary_key = 1/*主键*/ ) PK ON Tab.object_id = PK.object_id AND Col.column_id = PK.column_id LEFT JOIN sys.computed_columns computed_columns ON Tab.object_id = computed_columns.object_id AND Col.column_id = computed_columns.column_id LEFT JOIN sys.extended_properties Col_Desc ON Col_Desc.major_id = Tab.object_id AND Col_Desc.minor_id = Col.Column_id AND Col_Desc.class = 1WHERE Tab.type = 'U' AND Tab.Name NOT LIKE'sys%'ORDER BY Tab.create_date