通过各种查资料,并且阅读sqlserver自带的存储过程总结出来的
SELECT DISTINCT c.name AS tablename, a.name,a.is_nullable,dbo.GetRemoveParentheses(d.text) AS defaultvalue,a.is_identity,
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)
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
LEFT JOIN sys.key_constraints e ON e.parent_object_id = a.object_id
LEFT JOIN sys.indexes f ON e.unique_index_id = f.index_id AND f.object_id = a.OBJECT_ID
WHERE c.xtype = 'u'
函数[GetRemoveParentheses]代码如下
USE [Academe_DBAdmin]
GO
/****** Object: UserDefinedFunction [dbo].[GetRemoveParentheses] Script Date: 2015/5/29 15:36:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetRemoveParentheses](@words varchar(200))
RETURNS varchar(200)
AS
BEGIN
WHILE(LEFT(@words,1) = '(' AND RIGHT(@words,1) =')')
BEGIN
SELECT @words = SUBSTRING(@words,2,LEN(@words)-2)
END
RETURN @words
END