获取数据库表格字段描述

USE [database1]
GO
/****** Object: StoredProcedure [dbo].[Sp_ObjItems] Script Date: 2024/7/12 13:17:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[CheckFormDescription]
-- Add the parameters for the stored procedure here
@tablename varchar(200)
AS
BEGIN

declare @dbname nvarchar(40) = db_name()

if (SELECT count(1) from sysobjects where id=object_id(@TableName)) > 0
begin
if ( select IIF(@tablename like '%[吖-座]%',1,0))=1
begin

		select a.[name] 表名,b.[value] 表说明
		from sysobjects a
		LEFT JOIN sys.extended_properties b ON a.id = b.major_id AND b.minor_id = 0
		where xtype = 'u'
		and  (cast(b.[value] as varchar)  like   N'%'+@tablename+'%'  or cast(a.[name] as varchar)   like N'%'+@tablename+'%' )

		select FrmName,MenuTitle
		from    database2.dbo.SYS_Menu  
		where (  MenuTitle like   N'%'+@tablename+'%'   or  MenuText like N'%'+@tablename+'%'  )
	--查询所包含视图
	if Exists(
			select distinct 'View/SP' [View/SP], a.name
			from sysobjects a, syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		)
		Begin
			select distinct 'View/SP' [View/SP], a.name 
			from sysobjects a, syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		End

	end
	else
	begin
	SELECT    
			表名       = Case When A.colorder=1 Then D.name When A.colorder=2 Then D.name else '' End,    
			表说明     = Case When A.colorder=1 Then isnull(F.value,'') When A.colorder=2 Then isnull(F.value,'')  Else '' End,   
			字段序号   = A.colorder,    
			字段名     = A.name,    
			字段说明   = isnull(G.[value],''),    
			标识       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,    
			主键       = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (    
							SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,    
			类型       = B.name,    
			占用字节数 = A.Length,    
			长度       = COLUMNPROPERTY(A.id,A.name,'PRECISION'),    
			小数位数   = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),    
			允许空     = Case When A.isnullable=1 Then '√'Else '' End,    
			默认值     = isnull(E.Text,'')    
		FROM    
			syscolumns A    
		Left Join    
			systypes B  ON  A.xusertype=B.xusertype    
		Inner Join    
			sysobjects D  ON  A.id=D.id  and D.xtype='U' and  D.name<>'dtproperties'    
		Left Join    
			syscomments E  ON  A.cdefault=E.id    
		Left Join    
		sys.extended_properties  G  ON  A.id=G.major_id and A.colid=G.minor_id    
		Left Join    
		sys.extended_properties F  On  D.id=F.major_id and F.minor_id=0 
  
	where d.name=replace(replace(@tablename,'[',''),']','')    --如果只查询指定表,加上此条件    
		Order By    
			A.id,A.colorder    
			--查询所包含视图
			if Exists(
			select distinct 'View/SP' [View/SP] , a.name
			from sysobjects a, syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		)
		Begin
			select distinct 'View/SP' [View/SP], a.name 
			from sysobjects a, syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		End
	end

end
ELSE
BEGIN
IF @dbname = 'database1'
BEGIN
--------------------------------------------------------------------
if ( select IIF(@tablename like '%[吖-座]%',1,0))=1
begin

		select a.[name] 表名,b.[value] 表说明
		from database1.dbo.sysobjects a
		LEFT JOIN sys.extended_properties b ON a.id = b.major_id AND b.minor_id = 0
		where xtype = 'u'
		and  (cast(b.[value] as varchar)  like   N'%'+@tablename+'%'  or cast(a.[name] as varchar)   like N'%'+@tablename+'%' )

		select FrmName,MenuTitle
		from    database2.dbo.SYS_Menu  
		where (  MenuTitle like   N'%'+@tablename+'%'   or  MenuText like N'%'+@tablename+'%'  )
	--查询所包含视图
	if Exists(
			select distinct 'View/SP' [View/SP], a.name
			from database2.dbo.sysobjects a, database2.dbo.syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		)
		Begin
			select distinct 'View/SP' [View/SP], a.name 
			from database2.dbo.sysobjects a, database2.dbo.syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		End

	end
	else
	begin
	SELECT    
			表名       = Case When A.colorder=1 Then D.name When A.colorder=2 Then D.name else '' End,    
			表说明     = Case When A.colorder=1 Then isnull(F.value,'') When A.colorder=2 Then isnull(F.value,'')  Else '' End,   
			字段序号   = A.colorder,    
			字段名     = A.name,    
			字段说明   = isnull(G.[value],''),    
			标识       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,    
			主键       = Case When exists(SELECT 1 FROM database2.dbo.sysobjects Where xtype='PK' and parent_obj=A.id and name in (    
							SELECT name FROM database2.dbo.sysindexes WHERE indid in( SELECT indid FROM database2.dbo.sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,    
			类型       = B.name,    
			占用字节数 = A.Length,    
			长度       = COLUMNPROPERTY(A.id,A.name,'PRECISION'),    
			小数位数   = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),    
			允许空     = Case When A.isnullable=1 Then '√'Else '' End,    
			默认值     = isnull(E.Text,'')    
		FROM    
			database2.dbo.syscolumns A    
		Left Join    
			database2.dbo.systypes B  ON  A.xusertype=B.xusertype    
		Inner Join    
			database2.dbo.sysobjects D  ON  A.id=D.id  and D.xtype='U' and  D.name<>'dtproperties'    
		Left Join    
			database2.dbo.syscomments E  ON  A.cdefault=E.id    
		Left Join    
		sys.extended_properties  G  ON  A.id=G.major_id and A.colid=G.minor_id    
		Left Join    
		sys.extended_properties F  On  D.id=F.major_id and F.minor_id=0 
  
	where d.name=replace(replace(@tablename,'[',''),']','')    --如果只查询指定表,加上此条件    
		Order By    
			A.id,A.colorder    
			--查询所包含视图
			if Exists(
			select distinct 'View/SP' [View/SP] , a.name
			from database2.dbo.sysobjects a, database2.dbo.syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		)
		Begin
			select distinct 'View/SP' [View/SP], a.name 
			from database2.dbo.sysobjects a, database2.dbo.syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		End
	end
	--------------------------------------------------------------------
END
ELSE
BEGIN
	--------------------------------------------------------------------
	if ( select IIF(@tablename like '%[吖-座]%',1,0))=1
	begin 

		select a.[name] 表名,b.[value] 表说明
		from database1.dbo.sysobjects a
		LEFT JOIN sys.extended_properties b ON a.id = b.major_id AND b.minor_id = 0
		where xtype = 'u'
		and  (cast(b.[value] as varchar)  like   N'%'+@tablename+'%'  or cast(a.[name] as varchar)   like N'%'+@tablename+'%' )

		select FrmName,MenuTitle
		from    database2.dbo.SYS_Menu  
		where (  MenuTitle like   N'%'+@tablename+'%'   or  MenuText like N'%'+@tablename+'%'  )
	--查询所包含视图
	if Exists(
			select distinct 'View/SP' [View/SP], a.name
			from database1.dbo.sysobjects a, database1.dbo.syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		)
		Begin
			select distinct 'View/SP' [View/SP], a.name 
			from database1.dbo.sysobjects a, database1.dbo.syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		End

	end
	else
	begin
	SELECT    
			表名       = Case When A.colorder=1 Then D.name When A.colorder=2 Then D.name else '' End,    
			表说明     = Case When A.colorder=1 Then isnull(F.value,'') When A.colorder=2 Then isnull(F.value,'')  Else '' End,   
			字段序号   = A.colorder,    
			字段名     = A.name,    
			字段说明   = isnull(G.[value],''),    
			标识       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,    
			主键       = Case When exists(SELECT 1 FROM database1.dbo.sysobjects Where xtype='PK' and parent_obj=A.id and name in (    
							SELECT name FROM database1.dbo.sysindexes WHERE indid in( SELECT indid FROM database1.dbo.sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,    
			类型       = B.name,    
			占用字节数 = A.Length,    
			长度       = COLUMNPROPERTY(A.id,A.name,'PRECISION'),    
			小数位数   = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),    
			允许空     = Case When A.isnullable=1 Then '√'Else '' End,    
			默认值     = isnull(E.Text,'')    
		FROM    
			database1.dbo.syscolumns A    
		Left Join    
			database1.dbo.systypes B  ON  A.xusertype=B.xusertype    
		Inner Join    
			database1.dbo.sysobjects D  ON  A.id=D.id  and D.xtype='U' and  D.name<>'dtproperties'    
		Left Join    
			database1.dbo.syscomments E  ON  A.cdefault=E.id    
		Left Join    
		sys.extended_properties  G  ON  A.id=G.major_id and A.colid=G.minor_id    
		Left Join    
		sys.extended_properties F  On  D.id=F.major_id and F.minor_id=0 
  
	where d.name=replace(replace(@tablename,'[',''),']','')    --如果只查询指定表,加上此条件    
		Order By    
			A.id,A.colorder    
			--查询所包含视图
			if Exists(
			select distinct 'View/SP' [View/SP] , a.name
			from database1.dbo.sysobjects a, database1.dbo.syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		)
		Begin
			select distinct 'View/SP' [View/SP], a.name 
			from database1.dbo.sysobjects a, database1.dbo.syscomments b
			where a.id=b.id
			and b.text like N'%'+@tablename+'%'
			and a.Name <> @tablename
		End
	end
	--------------------------------------------------------------------
END

END
END