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

来源：互联网发布：淘宝宝贝介绍图片尺寸编辑：程序博客网时间：2024/05/16 16:01

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

SELECT DISTINCT c.name AS tablename, a.name,a.is_nullable,dbo.GetRemoveParentheses(d.text) AS defaultvalue,a.is_identity,<span style="white-space:pre"></span>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)) AND f.is_primary_key = 1)<span style="white-space:pre"></span> THEN<span style="white-space:pre"></span> 1<span style="white-space:pre"></span> ELSE<span style="white-space:pre"></span> 0<span style="white-space:pre"></span> END<span style="white-space:pre"></span> AS isPrimary<span style="white-space:pre"></span> ,        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<span style="white-space:pre"></span>INNER JOIN sysobjects c ON c.id = a.object_id<span style="white-space:pre"></span>LEFT JOIN syscomments d ON d.id = a.default_object_id<span style="white-space:pre"></span>LEFT JOIN sys.key_constraints e ON e.parent_object_id = a.object_id<span style="white-space:pre"></span>LEFT  JOIN sys.indexes f ON e.unique_index_id = f.index_id AND f.object_id = a.OBJECT_ID <span style="white-space:pre"></span> WHERE c.xtype = 'u'

函数[GetRemoveParentheses]代码如下

USE [Academe_DBAdmin]GO/****** Object:  UserDefinedFunction [dbo].[GetRemoveParentheses]    Script Date: 2015/5/29 15:36:03 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[GetRemoveParentheses](@words varchar(200))RETURNS  varchar(200)ASBEGIN WHILE(LEFT(@words,1) = '(' AND RIGHT(@words,1) =')')    BEGINSELECT @words = SUBSTRING(@words,2,LEN(@words)-2)END RETURN @wordsEND

0 0