读取sqlserver用户自定义表、视图和存储过程

来源：互联网发布：社交软件英文翻译编辑：程序博客网时间：2024/06/06 00:18

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

0 0