/**************************************************************************************************************
、获取所有用户名:
islogin='1' :表示帐户
islogin='0' :表示角色
status='2' :表示用户帐户
status='0' :表示系统帐户
**************************************************************************************************************/SELECT nameFROM sysusersWHERE status = '0'
AND islogin = '1'/**************************************************************************************************************
、获取所有数据库名:
**************************************************************************************************************/SELECT nameFROM master..sysdatabasesORDER BY name/**************************************************************************************************************
、获取所有表名:
XType='U' :表示所有用户表;
XType='S' :表示所有系统表;
**************************************************************************************************************/SELECT nameFROM 数据库名..sysobjectsWHERE xtype = 'U'ORDER BY name/**************************************************************************************************************
、获取所有字段名:
**************************************************************************************************************/SELECT nameFROM syscolumnsWHERE id = Object_id('表名')/**************************************************************************************************************
、获取数据库所有类型:
**************************************************************************************************************/SELECT nameFROM systypes/**************************************************************************************************************
、获取主键字段:
**************************************************************************************************************/SELECT nameFROM syscolumnsWHERE id = Object_id('表名') AND colid = (SELECT TOP 1 keyno FROM sysindexkeys WHERE id = Object_id('表名'))/**************************************************************************************************************
、获取表字段的基本信息:
a.
**************************************************************************************************************/SELECT 字段名 = Rtrim(b.name)
,主键 = CASE
WHEN h.id IS NOT NULL THEN 'PK'
ELSE ''
END
,字段类型 = Type_name(b.xusertype) + CASE
WHEN b.colstat & 1 = 1 THEN '[ID('
+ CONVERT(VARCHAR,Ident_seed(a.name)) + ','
+ CONVERT(VARCHAR,Ident_incr(a.name)) + ')]'
ELSE ''
END
,长度 = b.length
,允许空 = CASE b.isnullable
WHEN 0 THEN 'N'
ELSE 'Y'
END
,默认值 = Isnull(e.TEXT,'')
,字段说明 = Isnull(c.VALUE,'')FROM sysobjects a INNER JOIN sys.all_objects aa ON a.id=aa.object_id
AND schema_name(schema_id)='dbo'
,syscolumns b LEFT OUTER JOIN sys.extended_properties c ON b.id = c.major_id AND b.colid = c.minor_id LEFT OUTER JOIN syscomments e ON b.cdefault = e.id LEFT OUTER JOIN (SELECT g.id
,g.colid FROM sysindexes f
,sysindexkeys g WHERE (f.id = g.id) AND (f.indid = g.indid) AND (f.indid > 0) AND (f.indid < 255) AND (f.status & 2048) <> 0) h ON (b.id = h.id) AND (b.colid = h.colid)WHERE (a.id = b.id) AND (a.id = Object_id('表名')) --要查询的表改成你要查询表的名称ORDER BY b.colid/**************************************************************************************************************
b.
**************************************************************************************************************/SELECT 表名 = CASE
WHEN a.colorder = 1 THEN d.name ELSE ''
END
,表说明 = CASE
WHEN a.colorder = 1 THEN Isnull(f.VALUE,'') ELSE ''
END
,字段序号 = a.colorder
,字段名 = a.name
,标识 = CASE
WHEN Columnproperty(a.id,a.name,'IsIdentity') = 1 THEN '√'
ELSE ''
END
,主键 = 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 = a.id AND colid = a.colid))) THEN '√'
ELSE ''
END
,类型 = b.name
,字段长度 = a.length
,占用字节数 = Columnproperty(a.id,a.name,'PRECISION')
,小数位数 = Isnull(Columnproperty(a.id,a.name,'Scale'),0)
,允许空 = CASE
WHEN a.isnullable = 1 THEN '√'
ELSE ''
END
,默认值 = Isnull(e.TEXT,'')
,字段说明 = Isnull(g.[value],'')FROM syscolumns a LEFT JOIN systypes b ON a.xusertype = b.xusertype INNER JOIN sysobjects d ON (a.id = d.id) AND (d.xtype = 'U') AND (d.name <> 'dtproperties')
INNER JOIN sys.all_objects c ON d.id=c.object_id
AND schema_name(schema_id)='dbo'
LEFT JOIN syscomments e ON a.cdefault = e.id LEFT JOIN sys.extended_properties g ON (a.id = g.major_id) AND (a.colid = g.minor_id) LEFT JOIN sys.extended_properties f ON (d.id = f.major_id) AND (f.minor_id = 0)--where d.name='要查询的表' --如果只查询指定表,加上此条件ORDER BY a.id
,a.colorder/***************************************************************************************************************
c. 前两种不完整~
****************************************************************************************************************/--获取数据库中所有数据表EXEC sp_tables
-- 获取数据表或列结构及描述信息EXEC sp_columns '表名'--2008新功能,获取数据表字段及描述信息SELECT *FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME='表名'-- 获取数据表字段的说明信息SELECT * FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', '表名', 'column', default)
-- 取得主索引EXEC sp_pkeys 表名
/**************************************************************************************************************
表及字段描述信息处理示例
**************************************************************************************************************/--创建表CREATE TABLE 表 (
a1 VARCHAR(10)
,a2 CHAR(2))--为表添加描述信息EXECUTE Sp_addextendedproperty N'MS_Description' , '人员信息表' , N'user' , N'dbo' , N'table' , N'表' , NULL , NULLEXEC sys.sp_addextendedproperty
@name=N'MS_Description', @value=N'人员信息表' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'表'
--为字段a1添加描述信息EXECUTE Sp_addextendedproperty N'MS_Description' , '姓名' , N'user' , N'dbo' , N'table' , N'表' , N'column' , N'a1'EXEC sys.sp_addextendedproperty
@name=N'MS_Description', @value=N'人员信息表' ,
@level0type=N'SCHEMA',@level0name=N'dbo',
@level1type=N'TABLE',@level1name=N'表',
@level2type=N'COLUMN',@level2name=N'a1'
--为字段a2添加描述信息EXECUTE Sp_addextendedproperty N'MS_Description' , '性别' , N'user' , N'dbo' , N'table' , N'表' , N'column' , N'a2'--更新表中列a1的描述属性:EXEC Sp_updateextendedproperty 'MS_Description' , '字段1' , 'user' , dbo , 'table' , '表' , 'column' , a1--显示表的描述属性SELECT *FROM ::fn_listextendedproperty(NULL,'user','dbo','table','表名',NULL,NULL)--显示表列的描述属性SELECT *FROM ::fn_listextendedproperty(NULL,'user','dbo','table','表','column',NULL)--删除表中列a1的描述属性:EXEC Sp_dropextendedproperty 'MS_Description' , 'user' , dbo , 'table' , '表' , 'column' , a1--删除测试DROP TABLE 表
--批量删除用户表--1.删除外键约束DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
from sysobjects
where xtype = 'F'open c1declare @c1 varchar(8000)fetch next from c1 into @c1while(@@fetch_status=0) begin
exec(@c1) fetch next from c1 into @c1
endclose c1deallocate c1
--2.删除表DECLARE c2 cursor for
select 'drop table ['+name +']; '
from sysobjects
where xtype = 'u' open c2declare @c2 varchar(8000)fetch next from c2 into @c2while(@@fetch_status=0) begin
exec(@c2) fetch next from c2 into @c2
endclose c2deallocate c2
--批量清除表内容:--1.禁用外键约束DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] nocheck constraint ['+name+']; '
from sysobjects
where xtype = 'F'open c1declare @c1 varchar(8000)fetch next from c1 into @c1while(@@fetch_status=0) begin
exec(@c1) fetch next from c1 into @c1
endclose c1deallocate c1
--2.清除表内容DECLARE c2 cursor for
select 'truncate table ['+name +']; '
from sysobjects
where xtype = 'u' open c2declare @c2 varchar(8000)fetch next from c2 into @c2while(@@fetch_status=0) begin
exec(@c2) fetch next from c2 into @c2
endclose c2deallocate c2--3.启用外键约束DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] check constraint ['+name+']; '
from sysobjects
where xtype = 'F'open c1declare @c1 varchar(8000)fetch next from c1 into @c1while(@@fetch_status=0) begin
exec(@c1) fetch next from c1 into @c1
endclose c1deallocate c1