SQL Server查询表结构语句

--1：获取当前数据库中的所有用户表 www.2cto.com

select Name from sysobjects where xtype='u' and status>=0

--2：获取某一个表的所有字段

select name from syscolumns where id=object_id('表名')

--3：查看与某一个表相关的视图、存储过程、函数

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

--4：查看当前数据库中所有存储过程

select name as 存储过程名称 from sysobjects where xtype='P'

--5：查询用户创建的所有数据库

select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')

或者

select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

--6：查询某一个表的字段和数据类型

select column_name,data_type from information_schema.columns

where table_name = '表名'

--7:获取一个表结构的字段，类型，描述，最大长度，

select
cast(t1.tables_name as nvarchar) tables_name,
cast(t1.columns_name as nvarchar) columns_name,
cast(t1.columns_type as nvarchar) columns_type,
cast(t1.max_length as int) max_length,
cast(t1.is_nullable as nvarchar) is_nullable,
cast(sys.extended_properties.value as nvarchar) description,
cast(t1.column_id as int) column_id
from
(
  select
   sys.tables.name tables_name,
   sys.columns.name columns_name,
   sys.types.name columns_type,
   sys.columns.max_length max_length,
   sys.columns.is_nullable is_nullable,
   sys.columns.object_id object_id,
   sys.columns.column_id column_id
  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.columns.user_type_id = sys.types.user_type_id
  and sys.tables.name = '#tablename#表名'
) t1
left join sys.extended_properties on sys.extended_properties.major_id = t1.object_id
and sys.extended_properties.minor_id = t1.column_id
order by
t1.column_id