SqlServer数据库常用操作收集

1.查询数据库中一张表的总行数

select top 1 rowcnt from sysindexes where id=object_id('D_TRACELEVL4')        --1937984220  瓶码
  select top 1 rowcnt from sysindexes where id=object_id('D_TRACELEVL3')        --89825045    箱码
  select top 1 rowcnt from sysindexes where id=object_id('D_TRACELEVL2')        --806093      垛码
  select top 1 rowcnt from sysindexes where id=object_id('D_TRACELEVL1')        --0
  select top 1 rowcnt from sysindexes where id=object_id('D_TRACELEVL4NOTREF')  --10427288    瓶码未关联表
  select top 1 rowcnt from sysindexes where id=object_id('D_TRACEREF')          --2027743030  瓶箱垛关联表
  select top 1 rowcnt from sysindexes where id=object_id('D_ORDERTRACEREF')     --2133150522  瓶箱垛与出库订单关联表

2.查找一张表的主键

Execute   sp_pkeys 'Sys_SystemConfig' 

3.批量插入数据库中某张表多条数据存储过程

ALTER PROCEDURE [dbo].[AddPortlCoutInfo]
(    
    @xml     varchar(max) = ''    -- 入参XML
)
AS
    SET NOCOUNT ON 
    BEGIN TRY
        DECLARE @paraXml XML;        
        BEGIN TRANSACTION --开始事务
        SET @paraXml = CONVERT(XML, @xml);
    
        INSERT INTO D_PortlCoutInfo(PortId,Count,Added,Note)
        SELECT T.C.value('@PortId' , 'varchar(50)'),
        T.C.value('@Count' , 'int'),
        T.C.value('@Added' , 'int'),
        T.C.value('@Note' , 'varchar(200)')
        FROM @paraXml.nodes('//PortInfo') as T(C)    
        
        COMMIT TRANSACTION --提交事务
        SET NOCOUNT OFF
        RETURN 0
    END TRY
    BEGIN CATCH
        /**//*-------- 事务回滚 --------*/
        ROLLBACK TRANSACTION
        SET NOCOUNT OFF 
        RETURN   -1  
    END CATCH
    
    
--XML数据格式
--<?xml version="1.0"?>
--<Info>
--  <PortInfo PortId= "1"  Count="1" Added="1" Note="灌装机入口" />
--    <PortInfo PortId= "2"  Count="2" Added="2" Note="灌装机出口" />
--</Info>'

4.数据库执行一条数据的执行时间

set statistics profile on 
set statistics io on
set statistics time on 
go

《sql语句》

go

set statistics profile off 
set statistics io off
set statistics time off

5.数据分页查询

USE [SmartPlatform_DEBUG]
GO
/****** Object:  StoredProcedure [dbo].[GetRecordByPage]    Script Date: 08/03/2015 12:50:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetRecordByPage]
    @PageSize int, -- 每页的记录条数
    @PageNumber int, --当前页
    @QuerySql varchar(max),--查询字符串
    @KeyField varchar(500), -- 排序的字段
    @Sort varchar(20)='asc',-- 顺序还是逆序
    @total int output       -- 数据的总行数
AS
BEGIN
    
    declare @sqlText as varchar(max) 
    declare @sqlcount as nvarchar(max)
    declare @i int
    
    set @sqlText = 'select * from(
                   select row_number() over(order by '+@KeyField+''+@Sort+') rowno,* from('+@QuerySql+') t
                   ) a
                   where rowno between '+RTRIM((@PageNumber-1)*@PageSize+1)+' and '+RTRIM(@PageNumber*@PageSize)
    
    set @sqlcount = N'select @ct=count(*) from('+@QuerySql+') t'
    
    exec sp_executesql @sqlcount,N'@ct int output',@ct=@i output
    
    set @total=@i
    
    exec(@sqltext)
    
END