mysql 查询数据库备注_查找数据库备注信息

USE [DBSYS] --数据库名字

GO

/****** Object: StoredProcedure [dbo].[sp_select_talberowName] Script Date: 01/14/2015 14:43:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/**-- =============================================

Author: xft

CteateDate: 2013-10-11

Description:查看表的完整字段信息

Remark:

ModifyHistory:修改 添加了字段长度和标识等信息

**/--=============================================

ALTER PROCEDURE [dbo].[sp_select_talberowName](@tablename varchar(max))AS

BEGIN

SET NOCOUNT ON;--declare @sql varchar(max)--set @tablename=@tablename----set @sql = 'select a.name,b.value----from sys.syscolumns a left join sys.extended_properties b on a.id=b.major_id AND a.colid = b.minor_id----Where a.ID=OBJECT_ID('''+@tablename+''')'

--set @sql='select e.name,c.[type],e.value from ((--select a.name,b.value--from sys.syscolumns a left join sys.extended_properties b on a.id=b.major_id AND a.colid = b.minor_id--Where a.ID=OBJECT_ID('''+@tablename+''')) as e--left join (--select sys.columns.name,sys.types.name as [type] from sys.columns,sys.tables,sys.types--where sys.tables.object_id=sys.columns.object_id and sys.types.user_type_id=sys.columns.user_type_id--and sys.tables.name='''+@tablename+''') as c--on e.name=c.name)'

--print @sql--exec (@sql)

DECLARE @sql Nvarchar(2000)SET @sql = 'SELECT

字段序号=a.column_id,

字段名=a.name,

类型=b.name,

长度=a.max_length,

小数位数=a.scale,

标识=case when a.is_identity = 1 THEN'+''''+'是'+''''+'else'+ ''''+''+'''' +'end,

主键=case when exists(SELECT 1 FROM sys.objects where type='+''''+'PK'+''''+'and name in (

SELECT name FROM sys.indexes WHERE index_id in(

SELECT index_id FROM sys.index_columns WHERE object_id = a.object_id AND column_id=a.column_id

))) then'+''''+'是'+''''+'else'+''''+''+''''+'end,

允许空=case when a.is_nullable=1 then'+''''+'是'+''''+'else'+''''+''+''''+'end,

默认值=isnull(e.text,'+''''+''+''''+'),

字段说明=isnull(g.[value],'+''''+''+''''+')

FROM sys.columns a

left join sys.types b on a.user_type_id=b.user_type_id

inner join sys.tables d on a.object_id=d.object_id

left join sys.syscomments e on a.default_object_id=e.id

left join sys.extended_properties g on a.object_id=g.major_id and a.column_id=g.minor_id

left join sys.extended_properties f on d.object_id=f.major_id and f.minor_id=0

WHERE d.name='+''''+@tablename+''''+'order by d.name,a.column_id'

EXEC sp_executesql @sql

END