获取sqlserver数据库详情

获取数据库存在哪些表

select * from INFORMATION_SCHEMA.TABLES

获取数据库存在那些表并且具体到有哪些列以及类型和定义的长度

select * from INFORMATION_SCHEMA.columns

获取所有表的创建时间以及修改时间等

select * from sys.tables

获取所有表以及列的说明

select * from sys.extended_properties

获取当前数据库的名字

select db_name()

获取所有表名以及表说明：

注意：当出现variant无法强制类型，需要cast(a.name as varchar(100))

//top 1000 是分页，当你的项目设有分页功能时，此语句不要
//row_number() over(order by a.object_id) as no 是每条数据显示一个数字，仅能作为显示，不能当条件
select  top 1000
  ROW_NUMBER() OVER (ORDER BY a.object_id) AS No, 
  cast(a.name as varchar(100)) as 表明,
  isnull(cast(g.[value] as varchar(100)),'-') AS 说明
from
  sys.tables a left join sys.extended_properties g
  on (a.object_id = g.major_id AND g.minor_id = 0)

获取某个表的列、类型、长度、是否为空、列说明信息： 注意：当出现variant无法转换类型，需要用cast(sys.columns.name as varchar(100))强转类型 进行非空判断时isnull(cast(g.[value] as varchar(100)),’-’)进行强转

declare @table_name as varchar(max)
set @table_name = '表名'
select sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable,
(select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as description
from sys.columns, sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name=@table_name order by sys.columns.column_id