Common SQL server code segments

Several common SQL Server statements without using GO -- query the number of rows in the database to print the table name and number of rows greater than 0. DECLARE @ TableNameVARCHAR (128) declarepostcursorforselectnamefromsys. tablesOpenPostCu

Several common SQL Server statements without using GO -- query the number of rows in the database to print the table name and number of rows greater than 0. DECLARE @ TableName VARCHAR (128) Declare PostCur Cursor For SELECT name FROM sys. tables Open PostCu

Several common SQL Server statements <无>

Export GO -- query the number of rows in the database to print the table name and number of rows greater than 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 INTSET @ ct = 0 DECLARE @ strSQL NVARCHAR (500) SET @ strSQL = 'select @ ct = Count (1) From '+ @ TableNameexec sp_executesql @ strSQL, n' @ ct Int OUTPUTIF, @ ct OUTPUTIF (@ ct> 0) BEGINprint 'table name' + @ TableNamePRINT 'number of data entries '+ Convert (varchar (32), @ ct) ENDFetch next From PostCur Into @ TableName End Close PostCur Deallocate PostCur tables query the total data volume of each TABLE in the database gocreate table # tbles (ID int IDENTITY (128), TableName varchar ), isHandle bit DEFAULT ('false'), TableRowCount int DEFAULT (0) insert into # tbles (TableName) SELECT name FROM s Ys. tablesDECLARE @ TableName varchar (128) DECLARE @ ID intSET @ ID = 0 select top 1 @ ID = ID, @ TableName = TableName FROM # tbles WHERE IsHandle = 'false' WHILE (@ ID! = 0) BEGINDECLARE @ isexists bitDECLARE @ RowCount intDECLARE @ SqlStr nvarchar (1000) SET @ SqlStr = 'select @ RowCount = Count (1) FROM '+ @ TableNameexec sp_executesql @ SqlStr, n' @ RowCount int output', @ RowCount OUTPUTUPDATE # tbles SET IsHandle = 'true ', tableRowCount = @ RowCount WHERE TableName = @ TableNamePrint @ RowCountPRINT @ TableNameSET @ ID = 0 SET @ RowCount = 0 SET @ SqlStr = ''select top 1 @ ID = ID, @ TableName = TableName FROM # tbles WHERE IsHandle = 'false' ENDSELECT * FROM # tblesDrop Table # tbles explain query results returned by the Stored Procedure GOcreate proc getdata2asselect 1 as r1, 2 as r2GOcreate table # temp (r1 int, r2 int) insert into # temp exec getdata2select * From # tempdrop table # temp merge query generate spliced string Gocreate 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) GOSelect 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 -------------------------------------------------------------------------- obtain the random string gocreate view [dbo]. [V_RAND] ASSELECT RAND1 = CONVERT (INT, RAND () * 26), RAND2 = RAND () * 2 GOCreate FUNCTION [dbo]. [f_GetRandStr] (@ len int, @ flag int) returns nvarchar (100) AS -- @ LEN output character length -- @ FLAG return value contains character 1: uppercase letter 2: lowercase letter 3: Combination of uppercase and lowercase letters 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 ENDGOSelect dbo. f_GetRandStr (30,3) explain simple parameter passing output GOdeclare @ i3 intexec sp_executesql n' Select @ i3 = @ i1 + @ i2 ', n' @ i1 int, @ i2 int, @ i3 int output', 1, 22, @ i3 outputSelect @ i3