Common SQL statements of mssqlserver

-- Obtain the table structure SELECTsyscolumns. name, policypes. name, syscolumns. isnullable, syscolumns. Tables, rule. xusertypesystypes. xusertypeANDsyscolumns. idobject_id (your table name) in SqlServer -- query the table

-- Obtain the SELECT syscolumns table structure in SqlServer. name, policypes. name, syscolumns. isnullable, syscolumns. length FROM syscolumns, policypes WHERE syscolumns. xusertype = policypes. xusertype AND syscolumns. id = object_id ('your table name') -- separately query the table

-- Obtain the table structure in SqlServer

SELECT syscolumns. name, policypes. name, syscolumns. isnullable,
Syscolumns. length
FROM syscolumns, policypes
WHERE syscolumns. xusertype = policypes. xusertype
AND syscolumns. id = object_id ('your table name ')

-- Separately query the incremental field of the table
Select [name] from syscolumns where
Id = object_id (n' your table name') and COLUMNPROPERTY (id, name, 'isidentity ') = 1

-- Obtain the table's primary and foreign key constraints
Exec sp_helpconstraint 'your table name'

-- Recursive call of custom functions

This method is applicable to retrieving all parent-class data without limit.

CREATE function sp_GetAllParentByClassID
(
@ ClassID int -- Parameter
)
Returns varchar (500)
As
Begin

Declare @ ParentClassID varchar (15) -- variable parent ID
Declare @ result varchar (500) -- variable @ result
Set @ result =''
-- Obtain the parent ID and ParentClassID Based on the passed ClassID.
SELECT @ ParentClassID = ParentClassID FROM tClass
Where ClassID = @ classID

If (@ ParentClassID <> 0) -- if it is not the root node
Begin
-- Pass the @ ParentClassID parent ID as the ClassID for self-calling
Set @ result = dbo. sp_GetAllParentByClassID (@ ParentClassID) + @ ParentClassID + '_'

End
Return @ result
End

In some SQL versions, if you run the preceding SQL statement in dbo. sp_GetAllParentByClassID (@ ParentClassID), an error is returned.
The reason is that dbo. sp_GetAllParentByClassID function is created, but not yet,
Call dbo. sp_GetAllParentByClassID (@ ParentClassID) Here, so the system will prompt that this object does not exist,
The solution is to remove dbo. sp_GetAllParentByClassID (@ ParentClassID) And then Alter it!

-- How to put exec execution results into the variable num (custom SQL statement output parameters)

Declare @ num int,
@ Sqls nvarchar (4000)
Set @ sqls = 'select @ a = count (*) from tablename'
Exec sp_executesql @ sqls, n' @ a int output', @ num output
Select @ num

-------------- Statistical class -------------

Select zip from CustomersWHEREState = ''ky ''GROUP BY ALL ZIP
Select zip from CustomersWHEREState = ''ky ''GROUP BY ZIP
Select zip, Count (ZIP) AS CustomersByZIP FROM Customers group by zip having Count (ZIP) = 1
SELECT OrderID, Sum (Cost * Quantity) AS OrderTotal FROM Orders group by OrderID
SELECT Customer, OrderNumber, Sum (Cost * Quantity) AS OrderTotal FROM Orders group by Customer, OrderNumber WITH ROLLUP
SELECT Customer, OrderNumber, Sum (Cost * Quantity) AS OrderTotal FROM Orders group by Customer, OrderNumber WITH CUBE
-----------------------------------
When the CUBE results are confusing (this is often the case), you can add a GROUPING function, as shown below:

Select grouping (Customer), OrderNumber, Sum (Cost * Quantity) AS OrderTotal FROM Orders group by Customer, OrderNumber WITH CUBE

Each line in the result contains two additional values:
Value 1 indicates that the value on the left is a statistical value, which is a ROLLUP or CUBE operator.
0 indicates that the value on the left is a detailed record generated BY the initial group by statement.
----------------
SELECT region, SUM (population), SUM (area) FROM bbc group by region having sum (area)> 1000000

-------------------------------------

Select
Course name,
[Score> = 85] = SUM (Case When score> = 85 Then 1 Else 0 End ),
[85> score> 70] = SUM (Case When score> = 70 And score <85 Then 1 Else 0 End ),
[Score <60] = SUM (Case When score <60 Then 1 Else 0 End ),
Total Count = Count (1)
From TableName
Group By course name
Order By course name

---------------- Grouping segmentation statistics -----------------------

Select
T. fromaccountid,
Onglogtimes = sum (case when t. logtimes1> 0 then 1 else 0 end ),
Twologtimes = sum (case when t. logtimes2 = 2 then 1 else 0 end ),
Regnum = sum (t. regStatus ),
T. regdate
From
(Select
A. fromaccountid,
Count (1) as logtimes1, -- statistics of Logon records on the current day
Sum (case when datediff (mm, regdate, datetime) = 0 then 1 else 0 end) as logtimes2, -- logon statistics for the current month during registration
Max (case when datediff (dd, regdate, datetime) = 0 then 1 else 0 end) as regStatus, -- statistics of registration operations on the current day
Convert (char (10), a. regdate, 120) as regdate
From
Vgameuser a, loginlog B
Where
A. accountid = B. playerid
Group
A. fromaccountid, convert (char (10), a. regdate, 120) t
Group
T. fromaccountid, t. regdate

--------------------------