1. INSERT INTOUSE PUBS INSERT INTO MyBooks Select Title_ID, Title, Type From Titles where type = 'mod_cook'2. Coalesce Returns the first non-empty expression in its parameters. Coalesce (Expression1, ... n) with this Case function equivalent: Case when (Expression1 is not null) THEN Expression1 ... when (Expressionn is not null) THEN Expressionn Else NULL
3.Truncate Table Syntax: Truncate Table Nametruncate Table is functionally identical to the delete statement without WHERE clauses: all of them delete all rows in the table. But Truncate Table is fast than the Delete, and the system and transaction log resources are used. Every time the delete statement deletes a row, and records one per line of the deleted log in the transaction log. TRUNCATE TABLE deletes data by releasing the data page used by the storage table data and only the release of the page is only in the transaction log. Truncate Table Deletes all rows in the table, but the table structure and its columns, constraints, indexes remain unchanged. The count value used for the new row identification is reset to the seeds of the column. If you want to keep the identification count value, use Delete. If you want to delete a table definition and its data, use the Drop Table statement. For tables referenced by the Foreign Key constraint, you cannot use truncate table, but you should use the DELETE statement without WHERE clauses. Since Truncate Table is not recorded in the log, it cannot activate the trigger. TRUNCATE TABLE cannot be used to participate in the table of the index view.
4. Summary data 1 Summary data with CUBE 2 Use Rollup summary data to generate a report containing small and total reports, the ROLLUP operator is useful. The result set generated by the ROLLUP operator is similar to the result set generated by the Cube operator. The difference between CUBE and Rollup is that the result set generated by CUBE displays all combinations of the selected column value. The result set generated by the Rollup showed the aggregation of a hierarchy of the selected column value. The result set of the Rollup operation has the functionality similar to the result set returned by Compute By; however, Rollup has the following advantages: ROLLUP returns a single result set; Compute By returns multiple result sets, while multiple result rally increases the complexity of application code . ROLLUP can be used in server cursors; Compute By is not. Sometimes, the execution plan generated by the query optimizer is more efficient than the COMPUT BY generated.
3 Summary data with Compute and Compute By provides compute and compute BY to be backward compatible. The summary value generated by Compute is displayed as the result set of separated results in the query results.
5.SET ROWCOUNT causes Microsoft? SQL Server to stop processing queries after returning the specified row. Syntax: SET ROWCOUNT {Number | @number_var} It is recommended to rewrite the DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT to use Top syntax. For INSERT, UPDATE, and DELETE statements executed on remote tables and local and remote partition views, ignore the Set RowCount option setting To turn off this option (in order to return all rows), specify set rowcount as 0. Example: I want to change the original value "car" in the Flag field in the first line in the table "Car" to "Dining" set rowcount 13declare @i intset @ i = 0UPDATE A table set @ i = @ i 1, flag = case @i when 13 TEN 'Else Flag end6. ---------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------- Or above: SQL Server2000 Help
Source: 9CBS and other websites (there is no clear marking from 9CBS Forum question area) ---------------------------------------------------------------------------------------------------------- ---------------------------
101. How to get the default value of a field? Select column_default from information_schema.columns where table_name = 'table2' and colorn_name = 'colorn2'
102. List the information of all tables, field names, primary keys, types, lengths, and decimal numbers, and table names, A.ColORDER field serial number, A.COLORDER field sequence, A.Name field name, (Case When ColumnProperty (A.ID, A.NAME, 'ISIDENTITY') = 1 Then '√'lse' 'end) ID, (Case WHEN (Select Count (*) from sysobjects where (Name In (Id = A.ID) AND (INDID INDID from sysindexkeys where (id = a.id) and (select colid from syscolumn where (id = a.id) and Name = a.name)))))))))))))> 0 THEN '√' else '' end) Primary key, B.Name type, A.Length occupies bytes, ColumnProperty (A .id, a.name, 'precision') AS length, ISNULL (ColumnProperty (A.ID, A.NAME, 'Scale'), 0) AS number, (Case When A.isnullable = 1 Then '√' Else '' end) Allows empty, ISNULL (E.TEXT, '') default value, isnull (g. [value], '') AS field Description from syscolumns a left join systems b on a.xtype = B.XUSERTYPEINNNER JOIN SYSOBJECTS D on A.ID = D .id and d.xtype = 'u' and d.name <> DTPROPERTIES'LEFT JOIN SYSCOMMENTS EON A. CDEFAULT = E.IDeft Join Sysproperties Gon A.ID = g.id and a.colid = g.smallid Order By A.ID, A.ColORDER (Source: http:///dev.9cbs.net/Article/18/18624.shtm Title: Collection Several SQL Statements and Storage Processes LihongGen0 [Favorites])
103. Based on the data of the INSERT statement based on the table data Create Proc SpgeninSertsql (@tablename varchar (256)) asbegin declare @sql varchar (8000) Declare @sqlvalues varchar (8000) set @SQL = '(' set @sqlvalues = ' VALUES = @Sqlvalues COLS ' ' ',' ' ', @ SQL = @SQL '[' Name '],' from (SELECT CASE WHEN XTYPE IN (48 , 52, 56, 59, 60, 62, 104, 106, 108, 122, 127) THEN 'CASE WHEN' NAME 'IS NULL THEN' 'ELSE' CAST (' Name ' As Varchar) ' ' end 'WHEN XTYPE IN (58, 61) THEN 'CASE WHEN' NAME 'Is Null TEN' 'NULL' 'ELSE' '' '' '' ' ' 'CAST (' Name 'As Varchar)' ' ' '' '' '' 'end' WHEN XTYPE IN (167) THEN 'CASE WHEN' NAME 'IS NULL THEN' '' '' '' ELSE ' ' '' '' '' ' ' Replace (' Name ', '' '' '', '' '' '' '' '' '' '' '' ' ' End ' WHEN Xtype in (231) TEN 'CASE WHEN' NAME ' Is Null Ten '' Null '' Else ' ' ''
N '' '' '' 'Replace (' Name ',' '' '' '', '' '' '' '' ')' '' '' '' ' '' 'end' WHEN XTYPE IN (175) THEN 'CASE WHEN' NAME 'IS NULL THEN' '' '' '' ELSE ' ' '' '' '' ' ' Cast (Replace ' Name ', '' '' '' '' ') AS Char (' Cast As Varchar) ') ' '' '' ' '' end 'WHEN XTYPE IN (239) THEN' CASE WHEN ' NAME ' IS NULL THEN '' NULL '' ELSE ' ' '' '' '' '' ' ' Cast (Replace " ' Name ', '' '' '' '' ') AS Char (' Cast As Varchar) ') ' '' '' ' '' 'End' Else '' 'Null' 'End As Cols, Name from Syscolumns Where Id = Object_ID (@tablename)) T set @SQL =' SELECT '' INSERT INTO [' @tablename '] ' LEFT (@ SQL, LEN (@SQL) -1) ')' Left (@SqlValues, Len (@sqlvalues) -4) ')' from ' @ TableName --Print @SQL EXEC (@
SQL) Endgo (Source: http: //dev.9cbs.net/Article/18/18624.shtm Title: Collection Several SQL Statements and Storage Procedures LihongGen0 [Favorites] Thanks to Playyuer)
104. asbegin set nocount on declare @sqlstr varchar (4000) declare @ sqlstr1 varchar (4000) declare @ sqlstr2 varchar The table data generating an insert statement stored procedure 2CREATE proc proc_insert (@tablename varchar (256)) (4000) select @ sqlstr = 'select' 'insert' @ tablename select @ sqlstr1 = '' select @ sqlstr2 = '(' select @ sqlstr1 = 'values (' ' ' select @ sqlstr1 = @ sqlstr1 col ' ' ',' ' ', @ SQLSTR2 = @ SQLSTR2 Name ', 'from (Select Case - when a.xtype = 173 TEN' CASE WHEN ' A.NAME ' IS NULL THEN '' NULL '' ELSE ' ' CONVERT ( VARCHAR (' Convert (varchar (4), a.length * 2 2) '), ' a.name ') ' ' end 'when a.xtype = 104 THEN' CASE WHEN ' A. Name 'Is Null Then' 'Null' 'Else' 'Convert (VARCHAR (1),' a.Name ')' End 'When A.XTYPE = 175 TEN' CASE WHEN ' A.NAME ' Is Null Then '' '' '' Else ' ' '' '' '' ' ' 'Replace (' a.name ',' '' '' '",' '' '' ' '' '' '' '' '' '' '' '' '' End 'When A.XTYPE = 61 Then' Case When ' A.Name ' Is Null Ten '' Null '' Else ' ' '' '' '' '' ' ' Convert (varchar (23), '
A.NAME ', 121)' ' ' '' '' '' End 'When A. type = 106 TEN' CASE WHEN ' A.NAME ' Is Null Then '' Null 'Else ' ' Convert (VARCHART (VARCHAR (4), A.XPREC 2) '),' a.name ')' 'end' when a.xtype = 62 THEN 'CASE WHEN' A.Name 'Is Null Then' 'Null' 'Else' 'Convert (VARCHAR (23),' A.NAME ', 2)' 'end' when a.XTYPE = 56 THEN 'CASE WHEN' A.NAME 'Is Null Then' 'Null' 'Else' 'Convert (VARCHAR (11),' A.NAME ')' 'end' when a.xtype = 60 THEN 'CASE WHEN' A .name 'is null dam' 'Null' Else ' ' Convert (varchar (22), ' a.name ') ' ' end 'when a.xtype = 239 TEN' CASE WHEN ' A.NAME 'Is Null Then' 'Null' 'Else' '' '' '' '' ' ' Replace (' a.name ', '' '' '', '' '' ' '' '' '' '' '' '' '' '' '' when a.xtype = 108 TEN 'CASE WHEN' A.NAME 'IS NULL THEN' 'NULL' 'ELSE' 'Convert (VARCHAR (VARCHAR (4), A.XPREC 2) '), ' a.Name ') ' ' end 'when a.xtype = 231 TEN' CASE WHEN ' A .name '
Is Null Then '' '' '' Else ' ' '' '' '' ' ' 'Replace (' a.name ',' '' '' '",' '' '' ' '' '') ' ' End 'When A.XTYPE = 59 TEN' CASE WHEN A.NAME 'IS NULL THEN' 'NULL' 'ELSE' ' Convert (VARCHAR (23), ' A.NAME ', 2) ' ' end 'when a.xtype = 58 TEN' CASE WHEN ' A.NAME ' IS NULL THEN 'NULL' 'ELSE' ' '' '' '' '' 'CONVERT (VARCHAR (23),' a.name ', 121)' ' ' '' '' '' End 'When A.XTYPE = 52 THEN 'CASE WHEN' A.NAME 'IS NULL THEN' 'NULL' 'ELSE' 'Convert (varchar (12),' a.name ')' End 'When A.XTYPE = 122 THEN 'CASE WHEN' A.NAME 'IS NULL THEN' 'NULL' 'ELSE' 'Convert (varchar (22),' a.name ')' End 'When A.XTYPE = 48 THEN' Case when ' a.name ' Is Null Ten '' Null '' Else ' ' Convert (varchar (6), ' a.name ') ' ' end '- when a.xtype = 165 THEN' Case WHEN ' A.NAME ' IS NULL THEN '' NULL '' ELSE ' ' Convert (VARCHAR (VARCHAR (4), A.LENGTH * 2 2) '),' a.Name ')' '
End 'When A. type = 167 THEN' CASE WHEN ' A.NAME ' Is Null Then '' Null '' Else ' ' '' '' '' ' ' 'Replace (' A.Name ' , '' '' '' '' '' '' '' '' '' '' '' '' End 'Else' '' Null '' End AS Col, a.colid, a.name from syscolumns a where a.id = Object_id (@tablename) and a.xtype <> 189 and A.XTYPE <> 34 and A.XTYPE <> 35 and A.XTYPE <> 36 T order by colid select @ SQLSTR = @ SQLSTR LEFT (@ sqlstr2, len (@ SQLSTR2) -1) ')' Left (@ sqlstr1, len (@ SQLSTR1) -3) ')' 'from' @ Tablename-- Print @sqlstr EXEC (@sqlstr) set nocount offendgo (Source: http: ///dev.9cbs.net/Article/18/18624.shtm Title: Collection Several SQL statements and stored procedures LihongGen0 [Collection] Thank sky_BLUE)
105. Crack CDC Create Procedure SP_Decrypt (@Objectname VARCHAR (50)) ASBEGINSET NOCOUNT ON - 9CBS: J9988 Copyright: 2004.04.15 --v3.1 - Crack bytes are not restricted, suitable for SQLServer2000 stored procedures, Function, view, trigger - Correct the previous version of the video trigger does not decrypt the error correctly - Discover a wrong, please e_mail: 9cbsj9988@tom.combegin treage @ Objectname1 varchar (100), @ orgvarbin varbinary (8000) declare @ sql1 nvarchar (4000), @ sql2 varchar (8000), @ sql3 nvarchar (4000), @ sql4 nvarchar (4000) DECLARE @ OrigSpText1 nvarchar (4000), @ OrigSpText2 nvarchar (4000), @ OrigSpText3 nvarchar (4000), @resultsp nvarchar (4000) Declare @i int, @ status int, @ Type varchar (10), @ ParentId INTDECLARE @Colid Int, @ n int, @ @ @ @ j int, @ K int, @ encrypted int, @ Number Intselect @ type = xtype, @ parentid = parent_obj from sysobjects where id = object_id (@ObjectName) create table #temp (number int, colid int, ctext varbinary (8000), encrypted int, status int) insert #temp SELECT number, colid, ctext , Encrypted, Status from syscomments where id = Object_id (@Objectname) SELECT @ number = max (number) from #Tempset @ k = 0
while @k <= @ number beginif exists (select 1 from syscomments where id = object_id (@objectname) and number = @ k) beginif @ type = 'P'set @ sql1 = (case when @number> 1 then' ALTER PROCEDURE ' @Objectname '; ' RTRIM (@K) ' with encryption as' else 'alter procedure' @ Objectname 'with encryption as' end
if @ type = 'TR'begindeclare @parent_obj varchar (255), @ tr_parent_xtype varchar (10) select @ parent_obj = parent_obj from sysobjects where id = object_id (@objectName) select @ tr_parent_xtype = xtype from sysobjects where id = @ parent_objif @tr_parent_xtype = 'V'beginset @ sql1 =' ALTER TRIGGER ' @ objectname ' ON ' OBJECT_NAME (@parentid) ' WITH ENCRYPTION INSTERD OF INSERT AS PRINT 1 'endelsebeginset @ sql1 =' ALTER TRIGGER ' @ objectname ' ON ' Object_name (@parentid) 'with encryption for insert as print 1' endndif@ type = 'fn' or @ type = 'tf' or @ type = 'iF'set @ SQL1 = (Case @Type when' TF 'THEN' Alter Function ' @Objectname ' (@ a char (1)) Returns @B Table (a varchar (10)) with encryption as begin insert @B select @a return end 'when' fn 'Then'Alter Function' @ Objectname '(@ a char (1)) Returns char (1) with encryption as begin Return @a end'when' @objectname '(@ a char (1)) Returns Table with Encryption AS Return SELECT @a as aeend
IF @ type = 'V'SET @ SQL1 =' Alter View ' @ Objectname ' with Encryption As SELECT 1 AS F '
Set @ q = len (@ SQL1) set @ SQL1 = @ SQL1 Replicate ('-', 4000- @ q) SELECT @ SQL2 = Replicate ('-', 8000) set @ SQL3 = 'Exec (@ SQL1'Select @ colid = max (color) from #temp where number = @ K set @ n = 1WHILE @n <= ceiling (@ colid-1) / 2) And len (@ SQL3) <= 3996begin set @ SQL3 = @ SQL3 ' @' set @ n = @ n 1ndset @ SQL3 = @ SQL3 ')' EXEC SP_EXECUTESQL @ SQL3, N '@ SQL1 NVARCHAR (4000), @ sql1 = @ SQL1, @ = @ SQL2ENDSET @ K = @ K 1END
Set @ K = 0WHILE @K <= @ Number Begin
IF exists (SELECT 1 from syscomments where id = Object_id (@Objectname) and number = @ K) Beginselect @ colid = max (colid) from #temp where number = @ K set @ n = 1
While @n <= @ colorbeginselect @ OrigspText1 = ctext, @ encrypted = encrypted, @ status = status from #temp where colid = @ n and number = @ K
SET @ OrigSpText3 = (SELECT ctext FROM syscomments WHERE id = object_id (@objectName) and colid = @ n and number = @ k) if @ n = 1beginif @ type = 'P'SET @ OrigSpText2 = (case when @number> 1 THEN 'CREATE Procedure' @objectname ';' RTRIM (@K) 'with Encryption As' else 'Create Procedure' @ObjectName 'with encryption as' end)
IF @ type = 'fn' or @ type = 'tf' or @ type = 'iF'set @ origsptext2 = (case @Type when' TF 'TEN' CREATE FUNCTION ' @ObjectName ' (@ a char (1)) Returns @B Table (a varchar (10)) with encryption as begin insert @B select @a return end 'when' fn 'dam @Objectname ' (@ a char (1)) Returns char (1) WITH ENCRYPTION AS BEGIN RTURN @a end'when 'f' Then'create Function ' @Objectname ' (@ a char (1)) Returns Table with encryption as return select @a as a'end) if @ type = 'TR 'Begin
if @ tr_parent_xtype = 'V'beginset @ OrigSpText2 =' CREATE TRIGGER ' @ objectname ' ON ' OBJECT_NAME (@parentid) ' WITH ENCRYPTION INSTEAD OF INSERT AS PRINT 1 'endelsebeginset @ OrigSpText2 =' CREATE TRIGGER ' @ objectname ' ON ' Object_name (@parentid) ' with Encryption for Insert Asprint 1 'End
end
IF @ type = 'V'Set @ igsptext2 =' create view ' @ ObjectName ' with encryption as select 1 as f '
SET @ q = 4000-len (@ OrigsPText2) set @ origsptext2 = @ igigsptext2 replicate ('-', @ q) endelsebeginset @ igigsptext2 = replicate ('-', 4000) endset @ = 1
Set @Resultsp = Replicate (N'A ', (DatalesPtext1) / 2)))
While @i <= datalength (@ ptext1) / 2begin
Set @Resultsp = stuff (@Resultsp, @i, 1, nchar (unicode (@ ptext1, @i, 1)) ^ (unicode (@ ptext2, @i, 1)) ^ unicode (@ @ 1 OrigspText3, @i, 1)))))) SET @ i = @ i 1ndset @ orgvarbin = CAST (@ ptext1 as varbinary (8000)) set @ resultsp = (casten @ Encrypted = 1 Then @Resultsp Else Convert (nvarchar (4000), Case When @ status & 2 = 2 THEN UNCOMPRESS (@orgvarbin) Else @orgvarbin end) @Rint @Resultspset @ n = @ n 1
end
Endset @ k = @ K 1END
Drop Table #TemProllback Trand ---------------------- Reply to: ZJCXC (Zou Jian) Jiang Jian's decryption store process is very good, if you want to decrypt all The stored procedure, this is done:
- declare all the memory decryption procedure tb cursor for select name from sysobjects where xtype = 'P' and status> 0 declare @name sysname open tb fetch next from tb into @name while @@ fetch_status = 0 begin print '/ * ------- Store [' @ Name '] ----------- * / 'exec sp_decrypt @name fetch next from tb @Name End Close TB DEAALLOCATE TB
106. Change a field of the table to the automatic number, and keep its original value constant
- You can consider using the following stored procedures, but I suggest you modify it in the Enterprise Manager. / * Converts a field in the table to identification fields and retains the original value, because delete Original table,, if the table and other tables are associated, these associations are recreated - Zou Jian 2003.12 - * // * - Call the sample exec p_setid 'table name,' to convert the field name to "" /
If EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_setid]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_setid] Go
Create Proc P_Setid @ TBName Sysname, - To process the table name @fdname s1 varchame (8000), @ S2 varchar (8000), @TMPTB sysselect @ S1 = '' , @ S2 = ', @TMPTB =' [TMP _ ' @ TBNAME ' _ Bak] 'SELECT @ S1 = @ S1 ', [' Name '] Case Name when @fdname dam = Identity (Bigint, 1 1) 'else' 'end, @ S2 = @ S2 ', [' Name '] 'from syscolumns where object_id (@tbname) = idselect @ S1 = Substring (@ S1, 2, 8000), @ S2 = Substring (@ S2, 2, 8000) EXEC ('SELECT TOP 0' @ S1 'INTO' @ TMPTB 'from [' @ TBNAME '] set Identity_Insert' @ TMPTB 'OnInsert INTO' @ TMPTB '(' @ S2 ') SELECT' @ S2 'from [' @ TBNAME '] Set Identity_Insert' @ TMPTB 'Off') EXEC ('Drop Table [' @ TBNAME ') EXEC SP_RENAME @ TMPTB, @ TBNameGo
- Test
- Create Table Create Table Table (Name Bigint, Name VARCHAR (10)) Insert INTO Table Select 1, 'Zhang San' Union All Select 2, 'Lee Four' Union All Select 4, 'King Five' Go
- Call the stored procedure, change the number field to the identification field exec p_setid 'table', 'number' Go
- Display processing results Select * from table
- Display whether to modify success SELECT NAME from syscolumns where object_id ('table') = id and status = 0x80GO
- Delete Test DROP TABLE Table (Source: http://community.9cbs.net/expert/topic/2731/2731806.xml? Temp = .3456232)
107.
