sql Server 对 表 存储过程 信息 查询

sql Server 对 表 存储过程 信息 查询-数据库开发

–获取存储过程参数信息

select * from syscolumns where ID in   
  (SELECT id FROM sysobjects as a 
   WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1   
   and id = object_id(N'存储过程名'))

–获取表信息 1

复制代码
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 'true'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 'true' else '' end,
类型=b.name,
字节长度=a.length,
Prec=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
Scale=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then 'true'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
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='表名' --如果只查询指定表,加上此条件
order by a.id,a.colorder
复制代码

–获取表信息 2

exec sp_help 表名

— 复制表同表数据,在原表主键自增情况下使用

SET IDENTITY_INSERT sys_SystemMenu ON
此间复制  全选查询结果
SET IDENTITY_INSERT sys_SystemMenu OFF

复制代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[P_OutputData]    
@tablename sysname    
AS    
declare @column varchar(2000)    
declare @columndata varchar(2000)    
declare @sql varchar(8000)    
declare @xtype tinyint    
declare @name sysname    
declare @objectId int    
declare @objectname sysname    
declare @ident int    
set nocount on    
-- 判断对象是否存在    
set @objectId=object_id(@tablename)    
if @objectId is null    
begin    
print 'The object not exists'    
return    
end    
--此判断不严密    
set @objectname=rtrim(object_name(@objectId))    
if @objectname is null or charindex(@objectname,@tablename)=0    
begin    
print 'object not in current database'    
return    
end    
-- 判断对象是否是table    
if OBJECTPROPERTY(@objectId,'IsTable') < > 1    
begin    
print 'The object is not table'    
return    
end    
select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80    
if @ident is not null    
print 'SET IDENTITY_INSERT '+@TableName+' ON'    

declare syscolumns_cursor cursor   
for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid    
open syscolumns_cursor    
set @column=''    
set @columndata=''    
fetch next from syscolumns_cursor into @name,@xtype    
while @@fetch_status < >-1    
  begin    
    if @@fetch_status < >-2    
      begin    
        --if @xtype not in(189,34,35,99,98) --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理    
          begin    
            set @column=@column+case when len(@column)=0 then'' else ','end+'['+@name+']'       
            set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','end    
                +case when @xtype in(167,175) then '''''''''+['+@name+']+''''''''' --varchar,char    
                      when @xtype in(231,239) then '''N''''''+['+@name+']+''''''''' --nvarchar,nchar    
                      when @xtype=61 then '''''''''+convert(char(23),['+@name+'],121)+''''''''' --datetime    
                      when @xtype=58 then '''''''''+convert(char(16),['+@name+'],120)+''''''''' --smalldatetime    
                      when @xtype=36 then '''''''''+convert(char(36),['+@name+'])+''''''''' --uniqueidentifier    
                      else @name end    
          end    
      end    
    fetch next from syscolumns_cursor into @name,@xtype    
  end    
close syscolumns_cursor    
deallocate syscolumns_cursor    
set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename    
print '--'+@sql    
exec(@sql)    
if @ident is not null    
print 'SET IDENTITY_INSERT '+@TableName+' OFF'
复制代码

–分页存储过程

复制代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[p_ComPagingQuery]
(
    @tblName   varchar(255),       -- 表名
    @strGetFields varchar(1000) = '*',  -- 需要返回的列 
    @fldName varchar(1000)='',      -- 排序的字段名(如果排序类型为0则需要指定每个字段排序类型,如: EditorTime desc, SalesRecordNumber asc)
    @PageSize   int = 20,          -- 页尺寸
    @PageIndex  int = 1,           -- 页码
    @doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回
    @OrderType int = 0,  -- 设置排序类型, 1 自定义排序, 2升序 , 3降序
    @strWhere  nvarchar(max)='',  -- 查询条件 (注意: 不要加 where)
    @keyId varchar(50)  --主键ID
)
AS
declare @strSQL   varchar(max)       -- 主语句
declare @strSQLCount   varchar(max)       -- 总条数 主语句
declare @strTmp   varchar(110)        -- 临时变量
declare @strOrder varchar(max)        -- 排序类型
declare @oType varchar(10);   --升降序
if @doCount != 0-- 是否返回记录总数
begin

    if @strWhere !=''
    begin
        set @strSQLCount = 'select count(*) as Total from ' + @tblName + ' where '+@strWhere
    end
    else
    begin
        set @strSQLCount = 'select count(*) as Total from ' + @tblName 
    end
end  

--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况

if @OrderType != 1 --非自定义排序 根据表主键编号
begin

   set @oType=' desc';

   if @OrderType=2
   begin 
     set @oType=' asc';
   end
     set @strOrder = ' order by ' + @keyId+@oType;

--如果@OrderType不是0,就执行降序,这句很重要!
end
--自定义排序
else 
    if @fldName!=''
    begin
        set @strOrder = ' order by '+@fldName;
    end
    else
    begin
        set @strOrder = ' order by '+@keyId+' asc';
    end
if @PageIndex = 1
begin

    if @strWhere != ''   
        set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder
    else
        set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName + ' '+ @strOrder

--如果是第一页就执行以上代码,这样会加快执行速度

end
else
begin

--以下代码赋予了@strSQL以真正执行的SQL代码
if @strWhere != ''   
    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ' + @tblName + ' where ' + @strWhere + ' and ' 
    + @keyId + ' not in (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @keyId + ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') ' + @strOrder
else
     set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName + ' where ' + @keyId + ' not in (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @keyId + ' from ' + @tblName + ' ' + @strOrder + ') ' + @strOrder
end 

--end   

if @doCount != 0
begin
    --print(@strSQL+'; '+@strSQLCount)
    exec (@strSQL+'; '+@strSQLCount)
end
else
begin
    print(@strSQL)
    exec (@strSQL)-- select * from erp_wareHouse order by whname desc
end
    print(@strSQL)

标签