SQL server相关语句

-- 查询死锁
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName 
from sys.dm_tran_locks 
where resource_type='OBJECT'

-- 杀死死锁
kill 147

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