SQLServer常用代码段

几个常用的SQLServer语句 无 ------------------------------------------------------------------------GO--查询库中表的行数 打印大于0行的表名和行数DECLARE @TableName VARCHAR(128)Declare PostCur Cursor For SELECT name FROM sys.tables Open PostCu

几个常用的SQLServer语句 <无> $velocityCount-->

------------------------------------------------------------------------
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