频道栏目
首页 > 资讯 > Sybase > 正文

获取数据库所有列以及列的类型是否为空是否为默认值是否为主键

15-05-26        来源:[db:作者]  
收藏   我要投稿

通过各种查资料,并且阅读sqlserver自带的存储过程总结出来的

SELECT DISTINCT c.name AS tablename, a.name,a.is_nullable,dbo.GetRemoveParentheses(d.text) AS defaultvalue,
		CASE WHEN (a.name = index_col (c.name, f.index_id,  1) or
         a.name = index_col (c.name, f.index_id,  2) or
         a.name = index_col (c.name, f.index_id,  3) or
         a.name = index_col (c.name, f.index_id,  4) or
         a.name = index_col (c.name, f.index_id,  5) or
         a.name = index_col (c.name, f.index_id,  6) or
         a.name = index_col (c.name, f.index_id,  7) or
         a.name = index_col (c.name, f.index_id,  8) or
         a.name = index_col (c.name, f.index_id,  9) or
         a.name = index_col (c.name, f.index_id, 10) or
         a.name = index_col (c.name, f.index_id, 11) or
         a.name = index_col (c.name, f.index_id, 12) or
         a.name = index_col (c.name, f.index_id, 13) or
         a.name = index_col (c.name, f.index_id, 14) or
         a.name = index_col (c.name, f.index_id, 15) or
         a.name = index_col (c.name, f.index_id, 16))
		 THEN
		 1
		 ELSE
		 0
		 END
		 AS isPrimary
		 ,
        CASE a.precision
          WHEN 0
          THEN CASE a.is_ansi_padded
                 WHEN 1
                 THEN CONVERT(NVARCHAR(15), b.name + '('
                      + CONVERT(NVARCHAR(10), a.max_length) + ')')
                 WHEN 0 THEN b.name
               END
          ELSE CASE a.scale
                 WHEN 0 THEN b.name
                 ELSE b.name + '(' + CONVERT(NVARCHAR(10), a.precision) + ','
                      + CONVERT(NVARCHAR(10), a.scale) + ')'
               END
        END AS typelength 
FROM  sys.columns a
        LEFT JOIN sys.types b ON a.system_type_id = b.system_type_id
                                 AND a.user_type_id = b.user_type_id
		INNER JOIN sysobjects c ON c.id = a.object_id
		LEFT JOIN syscomments d ON d.id = a.default_object_id
		INNER JOIN sys.key_constraints e ON e.parent_object_id = a.object_id
		INNER JOIN sys.indexes f ON e.unique_index_id = f.index_id

WHERE c.xtype = 'u' AND f.is_primary_key = 1  


相关TAG标签
上一篇:cmd控制台插入mysql数据库中文信息时,插入失败的解决办法
下一篇:史上最严重的10次黑客袭击
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站