- ------------------------------------------------------------------------
- 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
- --该片段来自于http://www.codesnippet.cn/detail/030620133731.html
来源: http://www.codesnippet.cn/detail/030620133731.html