use master
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      mayb
-- Create date: 2015-03-11
-- Description: 根据条件查询数据库存储过程
-- =============================================
alter PROCEDURE prop_GetProps
@DbName nvarchar(100),          --数据库名称
@Name nvarchar(100),            --存储过程名称
@Definition nvarchar(500),      --存储过程主体
@BTcreate_date nvarchar(20),    --存储过程创建时间
@ETcreate_date nvarchar(20),    --存储过程创建时间
@BTmodify_date nvarchar(20),    --存储过程修改时间
@ETmodify_date nvarchar(20),    --存储过程修改时间
@startIndex int,                --开始索引
@endIndex int,                  --结束索引
@recordCount int output         --检索的数据总数
/*
declare @recordCount int
exec prop_GetProps '','','','','','','',1,999999,@recordCount
print @recordCount
*/
AS
BEGIN
    --查询条件
    declare @where varchar(max)=''
    --分页索引
    declare @pagingWhere varchar(max)= ' where rowNum between '+CAST(@startIndex as varchar(10))+' and '+CAST(@endIndex as varchar(10))+''
    --结果数
    declare @countSql nvarchar(max)=''
    --查询的SQL
    declare @sql varchar(max)=''
    --如果起始索引是1的时候，创建新的视图
    if(@startIndex=1)
        begin
            print @startIndex
            if(EXISTS(SELECT * FROM sys.views WHERE name = 'view_Props'))
                begin
                    drop view view_Props
                end
            if(@DbName!='')
                begin
                    set @sql='select a.name,a.[type],a.create_date,a.modify_date,b.[definition],'''+@DbName+''' as dbname from ['+@DbName+'].sys.all_objects a,['+@DbName+'].sys.sql_modules b where a.is_ms_shipped=0 and a.object_id = b.object_id and a.[type] in (''P'',''V'',''AF'')'
                end
            else
                begin
                    --select dbid,name from master..sysdatabases where dbid>4
                    declare @dbid int
                    declare @maxdbid int
                    select @dbid = min(dbid),@maxdbid=max(dbid) from master..sysdatabases where dbid>4
                    while @dbid is not null
                        begin
                            select @DbName=name from master..sysdatabases where dbid = @dbid
                            set @sql+='(select a.name,a.[type],a.create_date,a.modify_date,b.[definition],'''+@DbName+''' as dbname from ['+@DbName+'].sys.all_objects a,['+@DbName+'].sys.sql_modules b where a.is_ms_shipped=0 and a.object_id = b.object_id and a.[type] in (''P'',''V'',''AF''))'
                            if(@dbid != @maxdbid)
                                begin
                                    set @sql+=' union all '
                                end
                            select @dbid = min(dbid) from master..sysdatabases where dbid > @dbid
                        end
                end
            set @sql='create view view_Props as '+@sql
            exec(@sql)  
        end

    set @countSql='select @recordCount=count(*) from view_Props where 1=1'
    set @sql='select ROW_NUMBER() over(order by name asc) as rowNum,* from view_Props where 1=1'

    if(@Name!='')
        begin
            set @where+=' and name like ''%'+@Name+'%'' '
        end
    if(@Definition!='')
        begin
            set @where+=' and definition like ''%'+@Definition+'%'' '
        end
    if(@BTcreate_date!='' and @ETcreate_date!='')
        begin
            set @where+=' and create_date > '''+@BTcreate_date+''' and create_date < '''+@ETcreate_date+''''
        end
    else
        begin
            if(@BTcreate_date!='')
                begin           
                    set @where +=' and create_date > '''+@BTcreate_date+''''
                end
            if(@ETcreate_date!='')
                begin
                    set @where +=' and create_date < '''+@ETcreate_date+''''
                end
        end
    if(@BTmodify_date!='' and @ETmodify_date!='')
        begin
            set @where+=' and modify_date > '''+@BTmodify_date+''' and modify_date < '''+@ETmodify_date+''''
        end
    else
        begin
            if(@BTmodify_date!='')
                begin           
                    set @where +=' and modify_date > '''+@BTmodify_date+''''
                end
            if(@ETmodify_date!='')
                begin
                    set @where +=' and modify_date < '''+@ETmodify_date+''''
                end
        end

    set @countSql+=@where
    set @sql+=@where
    set @sql='select * from ('+@sql+') as t '+@pagingWhere

    exec(@sql)
    exec sp_executesql @countSql,N'@recordCount int out',@recordCount out

END
GO