时间:2021-07-01 10:21:17 帮助过:13人阅读
几个常用的SQLServer语句 无 ------------------------------------------------------------------------GO--查询库中表的行数 打印大于0行的表名和行数DECLARE @TableName VARCHAR(128)Declare PostCur Cursor For SELECT name FROM sys.tables Open PostCu
几个常用的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