1、获取所有数据库名:
SELECT Name FROM Master..SysDatabases ORDER BY Name
效果图:
2、 获取某数据库所有表名,直接选中一个数据库执行即可,不需要在SQL语句中指明数据库名
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
效果图:
3、得到所有表的表结构信息,跟上面一样,不需要在SQL语句中指明数据库名
SELECT (case when a.colorder=1 then d.name else null end) 表名,
a.colorder 字段序号,a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*) FROM sysobjects
WHERE (name in (SELECT name FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))
AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明]
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
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.class and f.minor_id=0
where b.name is not null
--WHERE d.name='要查询的表' --如果只查询指定表,加上此条件
order by a.id,a.colorder
效果图:
4、得到某个表的表结构信息
select
c.name as [字段名],t.name as [字段类型]
,convert(bit,c.IsNullable) as [可否为空]
,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in (
select name from sysindexes where indid in(
select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end)
as [是否主键]
,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长]
,c.Length as [占用字节]
,COLUMNPROPERTY(c.id,c.name,'PRECISION') as [长度]
,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]
,ISNULL(CM.text,'') as [默认值]
,isnull(ETP.value,'') AS [字段描述]
--,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]
from syscolumns c
inner join systypes t on c.xusertype = t.xusertype
left join sys.extended_properties ETP on ETP.major_id = c.id and ETP.minor_id = c.colid and ETP.name ='MS_Description'
left join syscomments CM on c.cdefault=CM.id
where c.id = object_id('表名')
效果图:(这个看上去还不错)
有了这些,自己就可以根据表结构写一个代码生成器了
内容来源:
http://www.cnblogs.com/sydeveloper/archive/2012/12/10/2811544.html
http://www.cnblogs.com/ynbt/archive/2012/07/16/2593389.html