首页 > 数据库 >获取数据库表格字段描述

获取数据库表格字段描述

时间:2024-07-12 14:54:19浏览次数:16  
标签:name 表格 数据库 SP tablename 获取 dbo id like

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

标签:name,表格,数据库,SP,tablename,获取,dbo,id,like
From: https://www.cnblogs.com/CoolYee/p/18298349

相关文章

  • excel表格中怎样将一列的数值全部加上一个数值
    1、打开目标文件             2、然后在B1或者其它单元格中输入数字“10”,如图所示。           3、然后鼠标右击此单元格,在右键菜单中执行“复制”命令。            4、然后选中要加10的所有数据,在右键......
  • 如何使用 Unity 的 ScriptableObjects 和 Resources 系统来创建一个简易的数据库系统,
    1.引言问题:如何在Unity中存储数据?解决方案:使用ScriptableObjects和Resources系统创建一个易于使用和管理的数据库。优势:自动读写数据自动处理资源实例化和更改无需额外插件使用用户定义键进行访问可扩展性强2.实现2.1项目结构四个主要的脚本:Item......
  • DedeCMS Error Warning!连接数据库失败,可能数据库密码不对或数据库服务器出错!
    DedeCMSErrorWarning!TechnicalSupport:http://bbs.dedecms.comErrorpage:/dede/login.phpErrorinfos:DedeCms错误警告:连接数据库失败,可能数据库密码不对或数据库服务器出错!这个错误信息是来自DedeCMS的内容管理系统,提示“连接数据库失败,可能数据库密码不对或数据库服......
  • 演示:【Avalonia-Controls】Avalonia皮肤,主题,自定义控件,数据库,系统模块资源库
    一、目的:分享一个Avalonia皮肤,主题,自定义控件,数据库,系统模块资源库开源地址:GitHub-HeBianGu/Avalonia-Controls:Avalonia控件库Nuget包地址:NuGetGallery|PackagesmatchingHeBianGu.AvaloniaUI.演示视频地址:【Avalonia-Controls】Avalonia工具组件皮肤库v1.0.0_......
  • 宝塔面板环境站点开启CDN后如何获取真实IP地址(多设置方法)
    这个问题是周末的时候帮助一个网友设置的,这里把问题简单的整理出来。这个同学有在使用宝塔面板搭建的WEB环境,且他的网站有开启和使用CDN加速,但是在加速之后呢日志中看不到真实的访客IP地址,而是CDN的节点IP,这个就不利于分析数据。所以,需要确保CDN能正常使用,而且日志的IP地址也能看......
  • element-ui表格中的复选框默认选中状态、禁用状态以及默认选中不可取消状态
    element-ui表格中的复选框默认选中状态、禁用状态以及默认选中不可取消状态_表格中el-checkbox默认显示选择-CSDN博客<el-tableref="multipleTable":data="tableData3"tooltip-effect="dark"style="width:100%"@selection-change="handleS......
  • Vue3中组件使用ref时获取组件类型推导
    本文由ChatMoney团队出品我们在使用Vue3+ts开发时,常常会用到一些第三方组件库,比如Element-PlusUI、NavieUI等,这些UI框架中有些组件常常会暴露一些方法给我们便捷的去实现各种复杂的交互,我们经常会像下面这样去给组件定义一个ref去获取组件的实例:<template><div><el-d......
  • 获取微信小程序页面路径
    2024/07/121.步骤2.注意事项3.参考1.步骤微信公众号关联小程序时需要用到小程序的页面路径,获取步骤如下:'登录微信公众平台——工具——生成小程序码——获取更多页面路径——填写微信号(好像不能用手机号替代)——点击开启——进入小程序——右上角三个点——左下角“复制页......
  • Vue3中组件使用ref时获取组件类型推导
    本文由ChatMoney团队出品我们在使用Vue3+ts开发时,常常会用到一些第三方组件库,比如Element-PlusUI、NavieUI等,这些UI框架中有些组件常常会暴露一些方法给我们便捷的去实现各种复杂的交互,我们经常会像下面这样去给组件定义一个ref去获取组件的实例:<template><div><el-d......
  • Spring Aop 获取入参和出参
    1.概述本次,我利用SpringAop的注解方式获取切入点的入参和出参,因为比较简单,所以就直接上代码了。2.代码编写注解类/***日志注解**@authorJiantaoYan*@title:Log*@date2021/1/520:33*/@Target({ElementType.METHOD})@Retention(RetentionPolicy.RUN......