SQLServer常用代码段

------------------------------------------------------------------------
GO
--查询库中表的行数 打印大于0行的表名和行数
DECLARE @TableName VARCHAR(128)
Declare PostCur Cursor For  
SELECT name FROM sys.tables  
Open PostCur  
Fetch next From PostCur Into @TableName  
While @@fetch_status=0  
    Begin  
    DECLARE @ct INT
    SET @ct=0
    DECLARE @strSQL NVARCHAR(500)
    SET @strSQL='Select @ct=Count(1) From '+@TableName
    exec sp_executesql @strSQL,N'@ct int output',@ct OUTPUT
    IF(@ct>0)
    BEGIN
        print '表名 '+@TableName
        PRINT '数据条数 '+Convert(varchar(32),@ct)
    END
    Fetch next From PostCur Into @TableName    
    End 
Close PostCur  
Deallocate PostCur
 
----------------------------------------
--查询数据库总各表数据量
GO
CREATE TABLE #tbles(ID int IDENTITY(1,1),TableName varchar(128)
    ,IsHandle bit DEFAULT('False')
    ,TableRowCount int DEFAULT(0))
INSERT INTO #tbles(TableName)
SELECT name FROM sys.tables
DECLARE @TableName varchar(128)
DECLARE @ID int
SET @ID=0
SELECT TOP 1 @ID=ID,@TableName=TableName FROM #tbles WHERE IsHandle='False'
WHILE(@ID!=0)
BEGIN
    DECLARE @isexists bit
    DECLARE @RowCount int
    DECLARE @SqlStr nvarchar(1000)
    SET @SqlStr='SELECT @RowCount=Count(1) FROM '+@TableName
    exec sp_executesql @SqlStr
    ,N'@RowCount int output',@RowCount OUTPUT
    UPDATE #tbles SET IsHandle='True',TableRowCount=@RowCount WHERE TableName=@TableName
    Print @RowCount
    PRINT @TableName    
    SET @ID=0
    SET @RowCount=0
    SET @SqlStr=''
    SELECT TOP 1 @ID=ID,@TableName=TableName FROM #tbles WHERE IsHandle='False'
END
SELECT * FROM #tbles
Drop Table #tbles
 
 
------------------------------------------------------------------------
---查询存储过程返回结果
GO
create proc getdata2
as
select 1 as r1,2 as r2
 
GO
create table #temp(r1 int,r2 int)
insert into #temp exec getdata2
select * From #temp
drop table #temp
 
 
------------------------------------------------------------------------
----查询生成拼接字符串
Go
create table #temp1(c1 int)
insert into #temp1(c1)Values(1)
insert into #temp1(c1)Values(2)
insert into #temp1(c1)Values(3)
insert into #temp1(c1)Values(4)
GO
Select c1 From #temp1 for xml path('')
Select c1 as [data()] From #temp1 for xml path('')
Select Convert(varchar(10),c1) +',' From #temp1 for xml path('')
declare @str varchar(max)
select @str=(Select Convert(varchar(10),c1) +',' From #temp1 for xml path(''))
select @str
 
 
------------------------------------------------------------------------
--获取随机字符串
GO
CREATE VIEW [dbo].[V_RAND]
AS
SELECT RAND1 = CONVERT(INT,RAND()*26),RAND2   =   RAND()*2
 
GO
Create   FUNCTION   [dbo].[f_GetRandStr](@LEN   INT,@FLAG   INT) 
RETURNS   NVARCHAR(100) 
AS
--@LEN   输出字符的长度 
--@FLAG   返回值包含字符   1:大写字母      2:小写字母      3:大小写字母混合 
BEGIN
       DECLARE   @SQL   NVARCHAR(100),@RAND   INT
       SELECT   @SQL   =    ''
       IF   @LEN>100 
             SET @LEN   =   100 
   
       WHILE   @LEN>0    
       BEGIN
             SELECT @RAND   =   RAND1 +(CASE   @FLAG   WHEN   1   THEN   65   WHEN   2   THEN   97 
                   ELSE(CASE WHEN RAND2 > 1 THEN   97   ELSE   65   END)   END) 
             FROM   V_RAND 
   
             SELECT   @SQL=@SQL + CHAR(@RAND),@LEN  = @LEN - 1 
       END
   
       RETURN   @SQL 
END
GO
Select dbo.f_GetRandStr(30,3)
 
----------------------------------------
--简单的传参输出
GO
declare @i3 int
exec sp_executesql N'Select @i3=@i1+@i2',N'@i1 int,@i2 int,@i3 int output',1,22,@i3 output
Select @i3