declare sql語句_那些你必須知道的sql(6/100)

大家好，我是天天。

這是我日更計劃的第6篇文章。

本來是想把枯燥的技術用有趣的方式表達出來，想了一下還是算了，今天比較喪，我才不會告訴你是因為我寫了個bug，導緻客戶損失了1k元，正在閉門思過中。

是以，這是一篇嚴肅的技術文。

本文列舉了本猿工作中經常用到的sql查詢，算是做一下記錄，避免踩坑。

--檢視每個表的行數SELECT   a.name, b.rowsFROM      sysobjects AS a INNER JOIN                 sysindexes AS b ON a.id = b.idWHERE   (a.type = 'u') AND (b.indid IN (0, 1))ORDER BY b.rows DESC
           

說明：複制表(隻複制結構,源表名：a 新表名：b) SQL: select * into b from a where 1<>1說明：拷貝表(拷貝資料,源表名：a 目标表名：b)SQL: insert into b(a, b, c) select d,e,f from b;
           

SELECT  ROW_NUMBER() OVER (ORDER BY CustomerID DESC)AS rn , CustomerID    INTO  # FROM  dbo.tb_Bonus_Cal    DECLARE @first INT ,@end INT  --循環次數    SET @first = 1    SET @end = (SELECT MAX(rn) FROM #)    PRINT @first    PRINT @end    --開始循環    WHILE @first <= @end    BEGIN        DECLARE @CustomerID BIGINT      --這個人的id        SET    @CustomerID =(SELECT CustomerID FROM #  WHERE rn = @first  )        PRINT 'aa'        SELECT * FROM @T        PRINT @CustomerI        SET @first = @first + 1    END
           

--循環處理表，很有用DECLARE @TableNameSuffix NVARCHAR(50)
           

SET @TableNameSuffix = 1WHILE @TableNameSuffix <= 50BEGIN  DECLARE @SqlStr NVARCHAR(MAX)  SET @SqlStr = '    ALTER TABLE dbo.tb_Wallet'[email protected]+' ADD FrozenBalance DECIMAL(19,2) DEFAULT 0 '  EXEC (@SqlStr)  SET @SqlStr = '    UPDATE dbo.tb_Wallet'[email protected]+' SET FrozenBalance = 0 '  EXEC (@SqlStr)SET @TableNameSuffix = @TableNameSuffix+1END
           

-- 删除日志USE [master]GOALTER DATABASE JRV SET RECOVERY SIMPLE WITH NO_WAITGOALTER DATABASE JRV SET RECOVERY SIMPLE --簡單模式GOUSE JRVGODBCC SHRINKFILE (N'dsis20081_Log' , 11, TRUNCATEONLY)GOUSE [master]GOALTER DATABASE JRV SET RECOVERY FULL WITH NO_WAITGOALTER DATABASE JRV SET RECOVERY FULL --還原為完全模式GO
           

使用下面語句檢視執行的查詢計劃SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql LIKE '%Users%'  and sql not like '%syscacheobjects%'
           

說明：複制表(隻複制結構,源表名：a 新表名：b) SQL: select * into b from a where 1<>1說明：拷貝表(拷貝資料,源表名：a 目标表名：b)SQL: insert into b(a, b, c) select d,e,f from b;
           

檢查資料庫表死鎖：SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName FROM sys.dm_tran_locks WHERE resource_type='OBJECT 'kill 查出來的ID，如kill 54
           

-- 生成資料歸零語句( 累計型字段和decimal型字段 )DECLARE @rzsql nvarchar(4000)  -- rz = return to zero( 歸零 )SELECT @rzsql = N'UPDATE tb_Bonus_Cal SET 'SELECT @rzsql = @rzsql + col. name +' = 0,'+ char(9)FROM sysobjects obj, syscolumns col, systypes stypeWHERE col. id = obj. idAND obj. xtype = 'U' AND col. xtype = stype. xtype AND stype. xtype = stype. xusertype AND obj. name = 'tb_Bonus_Cal'AND ( CHARINDEX( 'accu', col. name ) > 0 OR stype. name = 'decimal' ) -- 累計型和decimal型字段(注意：不能把int型也清零。因為lft，rgt是int型)SELECT @rzsql = substring( @rzsql, 1, len( @rzsql ) - 2 )  -- 截掉結尾的', '-- 執行資料歸零語句EXECUTE(@rzsql)