[SQL小白]技巧随得

1、将大量数据拆分成多个列表结果集

DECLARE @t INT, @bt INT,@dv INT
SET @dv=100
SELECT @bt=COUNT(*) FROM sys.objects
IF (@bt%@dv)<>0
BEGIN
SET @t=@bt/@dv + 1
END
ELSE
BEGIN
    SET @t=@bt/@dv 
END

SELECT *
,NTILE(@t) OVER(ORDER BY type ASC) AS 'splitter'
INTO #TEMP
FROM sys.objects

WHILE @t>0
BEGIN
    SELECT * 
    FROM #TEMP WHERE splitter=@t
    SET @t=@t-1
CONTINUE
END
DROP TABLE #TEMP

 

 

http://www.sqlservercentral.com/scripts/T-SQL/69860/ 

 

2. 一个SP

CREATEPROCEDURE[dbo].[StringSearchInSQLObject]
@TextToSearchVARCHAR(2000)
AS
SELECTDISTINCT
OBJECT_NAME(sc.id)AS'OBJECT NAME',
USER_NAME(so.uid)AS'OBJECT OWNER',
CASE
WHENso.xtype='C'THEN'CHECK constraint'
WHENso.xtype='D'THEN'DEFAULT or DEFAULT constraint'
WHENso.xtype='F'THEN'FOREIGN KEY constraint'
WHENso.xtype='L'THEN'Log'
WHENso.xtype='FN'THEN'Scalar function'
WHENso.xtype='IF'THEN'Inlined table-function'
WHENso.xtype='P'THEN'Stored procedure'
WHENso.xtype='PK'THEN'PRIMARY KEY constraint (type is K)'
WHENso.xtype='RF'THEN'Replication filter stored procedure'
WHENso.xtype='S'THEN'System table'
WHENso.xtype='TF'THEN'Table function'
WHENso.xtype='TR'THEN'Trigger'
WHENso.xtype='U'THEN'User table'
WHENso.xtype='UQ'THEN'UNIQUE constraint (type is K)'
WHENso.xtype='V'THEN'View'
WHENso.xtype='X'THEN'Extended stored procedure'
ENDAS'OBJECT TYPE',
CASE
WHENsc.encrypted=0THEN'Not encrypted'
WHENsc.encrypted=1THEN'Encrypted'
ENDAS'ENCRYPTED',
CASE
WHENsc.compressed=0THEN'Not compressed'
WHENsc.compressed=1THEN'Compressed'
ENDAS'COMPRESSED',
so.crdateAS'CREATE DATE',
sc.idAS'OBJECT ID'
FROMsyscomments scINNERJOINsysobjects soONsc.id=so.id
WHEREsc.textLIKE@TextToSearch
ORDERBY'OBJECT TYPE','OBJECT NAME'