读取sqlserver用户自定义表、视图和存储过程_sqlserver查看用户自定义表-优快云博客

本文链接：https://blog.youkuaiyun.com/tocytochyy/article/details/41674911

本文介绍了如何在SQLServer中获取用户自定义的存储过程、表和视图的信息。包括查询存储过程列表及其参数，获取所有表及字段详情，以及查看表的外键关联关系，并提供了相应的SQL查询语句。此外，还展示了获取所有视图的列表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.得到数据库存储过程列表：

select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name

1.1得到某个存储过程的参数信息：(SQL方法)

select * from syscolumns where ID in
(SELECT id FROM sysobjects as a
   WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1
   and id = object_id(N'[dbo].[mystoredprocedurename]'))

2.得到数据库所有表：

select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name

2.1得到某个表中的字段信息：

select c.name as ColumnName, c.colorder as ColumnOrder, c.xtype as DataType, typ.name as DataTypeName, c.Length, c.isnullable from dbo.syscolumns c inner join dbo.sysobjects t
on c.id = t.id
inner join dbo.systypes typ on typ.xtype = c.xtype
where OBJECTPROPERTY(t.id, N'IsUserTable') = 1
and t.name='mytable' order by c.colorder;

2.2 得到表外键关联关系

SELECT 主表名=object_name(b.rkeyid)
    ,主键列ID=b.rkey
    ,主键列名=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
    ,外键表ID=b.fkeyid
    ,外键表名称=object_name(b.fkeyid)
    ,外键列ID=b.fkey
    ,外键列名=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
    ,级联更新=ObjectProperty(a.id,'CnstIsUpdateCascade')
    ,级联删除=ObjectProperty(a.id,'CnstIsDeleteCascade')
FROM sysobjects a
    join sysforeignkeys b on a.id=b.constid
    join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'
order by object_name(b.rkeyid)

3 得到数据库所有视图：

select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsView') = 1 order by name