SQL Server operations

SQL Server operations:

1. Determine whether a table exists:

if exists(select * from sysobjects where id=OBJECT_ID(N'[' + @tableName + ']') and OBJECTPROPERTY(id,N'IsUserTable') = 1)

Compared to this database, OBJECT_ID gets an ID based on the table name, N represents the displayed conversion of non-Unicode characters to Unicode characters from the National (Unicode) data type in the SQL-92 standard. OBJECTPROPERTY: returns information about objects in the current database. 1 Table "true ".

 

2. Convert int to Char

@ Char = cast (@ int as varchar)

 

3. Use a cursor

-- Define the Cursor declare MyCursor Cursor forselect CompanyId from dbo. company where CompanyID in (7,29, 16) -- open the cursor open MyCursor -- loop cursor declare @ index intfetch next from MyCursor into @ indexwhile @ FETCH_STATUS = 0 beginexec ('drop table dbo. C '+ @ index +' _ DisplayPattern ') fetch next from MyCursor into @ indexend -- close the cursor close MyCursor -- release the resource deallocate MyCursor

 

4. Select the first few items and leave the same items

select TOP 4 with ties * from dbo.T_Personorder by FAge

 

5. SQL operator list

-- Control Character List in ms SQL:
-- Tab char (9)
-- Wrap char (10)
-- Press ENTER char (13)
-- Single quotes char (39)
-- Double quotation mark char (34)

 

6. Obtain the number of rows in a table.

set @sqls='select @a=COUNT(*) from [DemoSite_0213_OldCSV].[dbo].[C' + @company + '_Proposal]'exec sp_executesql @sqls,N'@a int output',@oldNumber output