--存储过程实现多表多字段分页查询语句
--1.判断此存储过程存在语句
if exists (
select 1
from sys.objects
where name = 'AdvanceDataQuery'
and type = 'p'
)
drop proc AdvanceDataQuery
go
--2.创建此存储过程语句
create proc AdvanceDataQuery
(
@TableName nvarchar(100), --表名
@Fields nvarchar(4000) = '*', --字段列表
@SqlWhere nvarchar(4000) = NULL, --条件语句
@OrderField nvarchar(4000), --排序字段
@PageIndex int, --当前页索引
@PageSize int, --页面大小
@TotalPage int output --总页面数
)
as
begin
begin tran
declare @Sql nvarchar(4000);
declare @TotalRecord int;
--以下计算总记录数
if (@SqlWhere = '' or @SqlWhere = NULL)
set @Sql = 'select @TotalRecord = count(*) from ' + @TableName
else
set @Sql = 'select @TotalRecord = count(*) from ' + @TableName
+ ' where ' + @SqlWhere
exec sp_executesql @Sql, N'@TotalRecord int output',
@TotalRecord output --得到记录总数
--以下计算总页数
select @TotalPage = CEILING((@TotalRecord + 0.0) / @PageSize)
if (@SqlWhere = '' or @SqlWhere = NULL)
set @Sql = 'select * from (select Row_Number() over ( order by '
+ @OrderField + ') as RowID, ' + @Fields + ' from '
+ @TableName + ' with (nolock)'
else
set @Sql = 'select * from (select Row_Number() over ( order by '
+ @OrderField + ') as RowID, ' + @Fields + ' from '
+ @TableName + ' with (nolock) where ' + @SqlWhere
--处理页数超出范围情况
if (@PageIndex <= 0)
set @PageIndex = 1
if (@PageIndex > @TotalPage)
set @PageIndex = @TotalPage
--处理开始点和结束点
declare @StartRecord int;
declare @EndRecord int;
set @StartRecord = (@PageIndex - 1) * @PageSize + 1
set @EndRecord = @StartRecord + @PageSize - 1
--继续合成sql语句
set @Sql = @Sql + ') as ' + @TableName + ' where RowID between '
+ convert(varchar(50), @StartRecord) + ' and '
+ CONVERT(varchar(50), @EndRecord)
--打印语句
print @Sql;
--执行语句
exec(@Sql)
if @@ERROR <> 0
begin
rollback tran
return -1
end
else
begin
commit tran
return @TotalRecord --返回记录总数
end
end
go
--3.使用此存储过程
DECLARE @TotalPage INT
EXEC AdvanceDataQuery 'College' , '*', '' , 'CollegeID', 5, 5 , @TotalPage
SELECT @@CONNECTIONS
SELECT @@CPU_BUSY / 100
SELECT @@IO_BUSY
EXEC sp_configure
--1：获取当前数据库中的所有用户表
SELECT Name
FROM sysobjects
WHERE xtype = 'u'
AND status >= 0
go
--2：获取某一个表的所有字段
SELECT name
FROM syscolumns
WHERE id = OBJECT_ID(N'表名')
go
--3：查看与某一个表相关的视图、存储过程、函数
SELECT a.*
FROM sysobjects a ,
syscomments b
WHERE a.id = b.id
AND b.text LIKE N'%表名%'
go
--4：查看当前数据库中所有存
储过程
SELECT name AS 存储过程名称
FROM sysobjects
WHERE xtype = 'p'
go
--4：查看当前数据库中所有表名
SELECT name AS 表名
FROM sysobjects
WHERE xtype = 'u'
go
--5：查询某用户创建的所有数据库
SELECT *
FROM master..sysdatabases D
WHERE sid NOT IN ( SELECT sid
FROM master..syslogins
WHERE name = 'sa' )
go
--或者
SELECT dbid ,
name AS DB_NAME
FROM master..sysdatabases
WHERE sid <> 0x01
go
--6：查询某一个表的字段和数据类型
SELECT column_name ,
data_type
FROM information_schema.columns
WHERE table_name = N'表名'
go
--7:获取某数据库文件路径
select ltrim(rtrim(filename))
from master..sysfiles
where charindex('MDF',filename)>0
go
--or
select ltrim(rtrim(filename))
from master..sysfiles
where charindex('LDF',filename)>0
go
--8:获取某一个表的基本信息
EXEC sp_MShelpcolumns N'表名'
go
--9:获取某一个表的主键、外键信息
exec sp_pkeys N'表名'
go
exec sp_fkeys N'表名'
go
--10:判断某一个表是否存在某一列(字段)
if exists(select 1 from syscolumns where id=object_id(N'表名') and name=N'字段')
print N'存在'
else
print N'不存在'
go