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

通过各种查资料，并且阅读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