SQLServer获取SQL所有用户名、数据库名、所有表名、所有字段名.docxVIP

1、获取所有用户名: Select name FROM Sysusers where status=2 and islogin=1 islogin=1 :表示帐户 islogin=0 :表示角色status=2 :表示用户帐户status=0 :表示糸统帐户 2、获取所有数据库名: Select Name FROM Master..SysDatabases orDER BY Name 3、获取所有表名: Select Name FROM DatabaseName..SysObjects Where XType=U orDER BY Name XType=U :表示所有用户表; XType=S :表示所有系统表; 4、获取所有字段名: Select Name FROM SysColumns Where id=Object_I表d(名  ) 5、获取数据库所有类型: select name from systypes 6、获取主键字段: Select name FROM SysColumns Where id=Object_Id表(名)and colid=(selecttop 1 keyno from sysindexkeys where id=Object表_I名d()) 7、获取表字段的基本信息: 程序代码Select 字段名=rtrim(b.name), 主键=CASE WHEN h.id IS NOT NULL THEN PK ELSE END, 字 段 类 型 =type_name(b.xusertype)+CASE WHEN b.colstat1=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, syscolumns b LEFT OUTER JOIN sysproperties c ON b.id = c.id AND b.colid = c.smallid LEFT OUTER JOIN syscomments e ON b.cdefault = e.id LEFT OUTER JOIN (Selectg.id,g.colidFROM sysindexesf, sysindexkeysg Where (f.id=g.id)AND(f.indid=g.indid)AND(f.indid0)AND(f.indid255)AND(f.status2048h)0) ON (b.id=h.id)AND(b.colid=h.colid) Where (a.id=b.id)AND(a.id=object要_i查d(询 的表)) --要查询的表改成你要查询表的名称 orDER BY b.colid 程序代码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 t√hen else end, 主键=case when exists(Select 1 FROM sysobjects where xtype=PK and name in (Select name FROM sysindexesWhere indid in(Selectindid FROM sysindexkeysWhere id=a.idAND colid=a.colid))) t√hen 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 默认值=isnull(e.text,), 字段说明=isnull(g.[value],) FROM syscolumns a √theenlse end, left join systypes b on a.xusertyp