SQL SERVER Monthly, quarterly, yearly statistics and common query statements Summary

First, SQL SERVER Monthly, quarterly, annual statistical query

--This day

SELECT *from dbo. TableName WHERE DATEDIFF (day,timefield,getdate ()) = 0;

--This week

SELECT *from dbo. TableName WHERE DATEDIFF (week,timefield,getdate ()) = 0;

--This month

SELECT *from dbo. TableName WHERE DATEDIFF (month,timefield,getdate ()) = 0;

--This quarter

SELECT *from dbo. TableName WHERE DATEPART (QQ, Timefield) = DATEPART (QQ, GETDATE ()) and DATEPART (yy, Timefield) = DATEPART (yy, GETDATE ());

--this year

SELECT *from dbo. TableName where DATEDIFF (year, timefield,getdate ()) = 0;

Note: TableName is the table name for the query, timefiled is the Time field for the query

Ii. SQL SERVER Determines whether tables, fields, stored procedures, triggers exist

--Determine if the table exists

SELECT *from sys.objects WHERE object_id = object_id (' table name ') and OBJECTPROPERTY (object_id, N ' isusertable ') = 1

-OR

SELECT *from sys.objects WHERE object_id = object_id (' table name ') and type= ' U '

--Determine if a field exists

SELECT *from sys.columns WHERE object_id = object_id (' field name ') and Name = ' No '

--Determine if the stored procedure exists

SELECT *from sys.objects WHERE object_id = object_id (' Stored procedure name ') and

OBJECTPROPERTY (object_id, N ' isprocedure ') = 1

-OR

SELECT *from sys.objects WHERE object_id = object_id (' Stored procedure name ') and type= ' P '

--Determine if the trigger exists

SELECT *from sys.objects WHERE object_id = object_id (' Trigger name ') and OBJECTPROPERTY (object_id, N ' istrigger ') = 1

-OR

SELECT *from sys.objects WHERE object_id = object_id (' Trigger name ') and type= ' TR '

Third, table copy statement

--Requires that table 2 does not exist and is automatically created when queried

Select field1,field2 from Table1 to Table2

--Requires that table 2 must exist and can be inserted into constants

Insert into table2 (field1,field2) Select Field1,field2 from table1

Iv. Related Updates

UPDATE Table1 SET t1f1 = t2f1, t1f2 = t2f2, t1f3 = t2f3 from Table1, Table2 WHERE table1.key = Table2.key

SQL SERVER Monthly, quarterly, yearly statistics and common query statements Summary