--查询数据库中有哪些表
select [name] from sysobjects where xtype= 'U'
--查询数据库中有哪些视图
select * from sysobjects where xtype = 'V' and left([name],1) ='V'
--查询数据库中有哪些存储过程
select * from sysobjects where xtype = 'P' and left([name],1) !='d'
--查询数据库中有哪些自定义函数
select [name] from sysobjects where xtype = 'FN'
--查询数据库中有哪些默认
select * from sysobjects where xtype ='D' and left([name],2) !='DF'
--------------------查询表描述---------------------
use jobcn_boss_sale
SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##tx
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.[name]='Wh_Sales_Onduty'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
----------------------查询视图描述----------------------------
use jobcn_boss_sale
SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##tx
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.[name]='V_ProtectCusAvailDate'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
----------------------查询存储过程的参数---------------------------
USE master
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PROC_Params]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PROC_Params]
GO
/*--查询存储过程的参数定义
查询存储过程,用户定义函数的参数定义
--邹建 2005.05(引用请保留此信息)--*/
/*--调用示例
EXEC sp_PROC_Params
@procedure_name='sp%',
@group_number=0,
@operator='%'
--*/
CREATE PROC sp_PROC_Params
@procedure_name sysname, --存储过程或者用户定义函数名
@group_number int=1, --存储过程的组号,必须在0到32767之间,0表示显示该存储过程组的所有参数
@operator nchar(2)=N'=' --查找对象的运算符
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SET @sql=N'SELECT
PorcedureName=CASE
WHEN o.xtype IN(''P'',''X'')
THEN QUOTENAME(o.name)+N'';''+CAST(c.number as varchar)
WHEN USER_NAME(o.uid)=''system_function_schema''
AND o.xtype=''FN''
THEN o.name
WHEN USER_NAME(o.uid)=''system_function_schema''
THEN ''::''+o.name
WHEN o.xtype=''FN''
THEN QUOTENAME(USER_NAME(o.uid))+N''.''+QUOTENAME(o.name)
ELSE QUOTENAME(o.name) END,
Owner=USER_NAME(o.uid),
GroupNumber=c.number,
ParamId=c.colid,
ParamName=CASE
WHEN o.xtype=''FN'' AND c.colid=0 THEN ''<Returns>''
ELSE c.name END,
Type=QUOTENAME(t.name)+CASE
WHEN t.name IN (''decimal'',''numeric'')
THEN N''(''+CAST(c.prec as varchar)+N'',''+CAST(c.scale as varchar)+N'')''
WHEN t.name IN (''float'',''real'')
OR t.name like ''%char''
OR t.name like ''%binary''
THEN N''(''+CAST(c.prec as varchar)+N'')''
ELSE '''' END,
Orientation=CASE
WHEN o.xtype=''FN'' AND c.colid=0 THEN ''<Returns>''
ELSE N''Input''
+CASE WHEN c.isoutparam=1 THEN ''/Output'' ELSE '''' END
END
FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id
AND c.xusertype=t.xusertype
AND o.name'
+CASE
WHEN @operator IN('=','>','>=','!>','<','<=','!<','<>','!=')
THEN @operator+QUOTENAME(@procedure_name,'''')
WHEN @operator='IN'
THEN @operator+N' IN('+QUOTENAME(@procedure_name,'''')+')'
WHEN @operator IN('LIKE','%')
THEN ' LIKE '+QUOTENAME(@procedure_name,'''')
ELSE '='+QUOTENAME(@procedure_name,'''')
END
+N'
AND(('
+CASE
WHEN @group_number BETWEEN 1 AND 32767
THEN N'c.number='+CAST(@group_number as varchar)
WHEN @group_number=0
THEN N'1=1'
ELSE N'c.number=1'
END+N' AND o.xtype IN(''P'',''X''))
OR (c.number=0 AND o.xtype=''FN'')
OR (c.number=1 AND o.xtype IN(''IF'',''TF'')))'
EXEC sp_executesql @sql
GO
--exec sp_PROC_Params 'Re_TractApplyList_Assist','',''
--select * from sysobjects where xtype='p' and name='sp_PROC_Params'
分享到:
相关推荐
数据库查询语句数据库查询语句数据库查询语句数据库查询语句
基本SQL数据库查询语句大全 让你能够很好的学习SQL,出基础知识开始了解
经典的数据库查询语句大全,包括增删改查及其他的更广泛的使用方法
包罗了所有的SQL语句 各种查询结构,各种情况下的查询,非常全面,很有帮助的!!
数据库查询语句数据库查询语句
数据库基础查询语句详解--此文本列举了上述博文中所有查询语句的代码,可以参考该文件进行数据库查询操作。
包括学生表,成绩表,科目表,老师表。练习查询语句的书写,可以练习内连接,自连接,排序,分组等查询的常用操作。
常用的数据库查询语句,包括单表查询,连接查询,嵌套查询,集合查询,很全面,适合作为查询手册
SQL数据库查询语句连接查询多表连接查询.docx
适合新手练习,熟悉SQL语句。 不会不要紧,依葫芦画瓢,慢慢地也就会了。
通用SQL数据库查询语句范例,丰富的查询语句可以帮助开发人员选取高效的查询方法
好的mysql数据库查询语句集合,非常实用,赶快下载奥。
sql查询语句,通用SQL数据库查询语句精华使用简介
通用SQL数据库查询语句精华使用
数据库查询语句.pdf
SQL数据库查询语句精华
SQL数据库查询语句范例.doc