IPAddr

SQL Syntax

This is my SQL Server T-SQL "Cheat Sheet". It is by no means an exhaustive list of possible SQL commands, combinations of SQL commands or even of the whole SQL language but it is a list of the common commands that I use on a day to day basis.


Select

Select Field1, Field2
From Table1
left outer join Table2
On Table1.Field3=Table2.Field4
Where Table1.Field5=25

This select function is the same as the one above but it uses a where clause instead of a join clause. This is a LESS efficient method of doing a join and should be avoided.

Select Field1, Field2
From Table1, Table2
Where Table1.Field3=Table2.Field4
And Table1.Field5=25


Grouping and Subtotalling

Select Field1, Max(Field2), Sum(Field6)
From Table1
left outer join Table2
On Table1.Field3=Table2.Field4
Where Table1.Field5=25
Group By Field1
Having Sum(Field6)>0


Update

Update Table1
Set Field1='ABC', Field2=23
Where Field5 Like 'D%'
And Field2=22


Delete

Delete From Table1
Where Field5='DEF'
And Field2=22

Delete From Table1
Where Field5='DEF'
And Field2 in (Select field3 from table2)

The following command is a select and a delete command in one. It deletes the records specified and returns a recordset containing the deleted records. You could then use foreign keys contained in the returned recordset to delete records from another table or just report on the data that was deleted.

Delete From Table1
Output Deleted.*
Where Field5='DEF'
And Field2=22


Insert

This function inserts two rows into an existing table.

Insert Into Table1
(Field1, Field2, field3}
Values
('value1',Value2,#value3#)
('value1',Value2,#value3#)

This function inserts one or more rows into an existing table from another table or view.

Insert Into Table1
(Field1, Field2, field3}
Select Field1, Field2, Field3
From Table2
Where Table2.Field5=25

This next command inserts data into a SQL table directly from a CSV format file

Bulk Insert Table1
From 'FileName'
With (FirstRow=1, FieldTerminator=',', RowTerminator = '\n')


Select Into

This function creates a NEW table and inserts one or more rows into it from another table or view.

Insert Field1, Field2, field Into Table2
From Table1
Where Table1.Field5=25


Wildcards


Aggregations


Joins


Nulls

Select IsNull(Field1,'') as Field4,
IsNull(Field2,0) as Field5,
IsNull(Field3,'2000-01-01') as Date1
From Table1
Where Field7=25

Delete from Table1
Where Field7 Is Null


Create View

A view is a virtual table. It contains no actual data - it is merely a stored SQL SELECT command but the results of a view can be treated as if it were a table. In many, but not all, cases you can even add data into an underlying table or delete records from an underlying table by executing SQL commands on the view itself.

Create View1 as
Select Field1, Field2
From Table1
left outer join Table2
On Table1.Field3=Table2.Field4
Where Table1.Field5=25

Create View ProdByLocn as
Select ProductCode as Code, ProductTitle,
ProductGroup as [Group], Sum(IsNull(QuantityOnHand, 0)) as QtyOH,
MileEnd=sum(Case When LocationNo=1 then IsNull(QuantityOnHand, 0) else 0 End)
From Products
Left Outer Join PLocDetails
on PLocDetails.ProductID=Products.UniqueID
Group By ProductCode, ProductTitle, ProductGroup

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'ProdByLocn'))
DROP VIEW ProdByLocn


Create and Drop Tables

CREATE TABLE TransactionTypes(
TransCode char(2) NOT NULL,
TransType char(50) NULL,
PrintDoc char(100) NULL,
LineItems char(100) NULL,
CONSTRAINT PK_TransactionTypes PRIMARY KEY CLUSTERED
(TransCode ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]) ON [PRIMARY]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TransactionTypes') AND type in (N'U'))
DROP TABLE TransactionTypes


Create and Drop Functions

CREATE function ProperCase (@expression nvarchar(4000), @Mode char(1)) returns nvarchar(4000) as
begin
declare @i smallint, @properexpression nvarchar(4000), @lenexpression smallint, @flag bit, @symbol nchar(1)
select @flag = 1, @i = 1, @properexpression = '', @lenexpression = datalength(@expression)/(case SQL_VARIANT_PROPERTY(@expression,'BaseType') when 'nvarchar' then 2 else 1 end)
while @i <= @lenexpression
begin
select @symbol = lower(substring(@expression, @i, 1) )
if (ascii(@symbol)>0
And ascii(@symbol)<48)
Or (ascii(@symbol)>57
And ascii(@symbol)<65)
Or (ascii(@symbol)>90
And ascii(@symbol)<97)
Or ascii(@symbol)>122
select @flag = 1
Else
if @flag = 1
select @symbol = upper(@symbol), @flag = 0
select @properexpression = @properexpression + @symbol, @i = @i + 1
If @Mode='U' Select @properexpression=Upper(@properexpression)
If @Mode='L' Select @properexpression=Lower(@properexpression)
End
return @properexpression
End

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'ProperCase')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION ProperCase


Create and Drop Stored Procedures

Create Procedure [dbo].[GenLongAddr] as
Begin
Update Customers
Set Deliveryaddress=Ltrim(Rtrim(Case When Rtrim(Isnull(Deliverystreet1,''))<>'' Then Rtrim(Deliverystreet1)+Char(13)+Char(10) Else '' End+
Case When Rtrim(Isnull(Deliverystreet2,''))<>'' Then Rtrim(Deliverystreet2)+Char(13)+Char(10) Else '' End+
Rtrim(Isnull(Deliverysuburb,''))+' '+Rtrim(Isnull(Deliverystate,''))+' '+Rtrim(Isnull(Deliverypostcode,''))));
Update CDelAddresses
Set Deliveryaddress=Ltrim(Rtrim(Case When Rtrim(Isnull(Deliverystreet1,''))<>'' Then Rtrim(Deliverystreet1)+Char(13)+Char(10) Else '' End+
Case When Rtrim(Isnull(Deliverystreet2,''))<>'' Then Rtrim(Deliverystreet2)+Char(13)+Char(10) Else '' End+
Rtrim(Isnull(Deliverysuburb,''))+' '+Rtrim(Isnull(Deliverystate,''))+' '+Rtrim(Isnull(Deliverypostcode,''))));
Update Customers
Set Mailingaddress=Ltrim(Rtrim(Case When Rtrim(Isnull(Mailingstreet1,''))<>'' Then Rtrim(Mailingstreet1)+Char(13)+Char(10) Else '' End+
Case When Rtrim(Isnull(Mailingstreet2,''))<>'' Then Rtrim(Mailingstreet2)+Char(13)+Char(10) Else '' End+
Rtrim(Isnull(Mailingsuburb,''))+' '+Rtrim(Isnull(Mailingstate,''))+' '+Rtrim(Isnull(Postcode,''))));
Update Suppliers
Set Mailingaddress=Ltrim(Rtrim(Case When Rtrim(Isnull(Mailingstreet1,''))<>'' Then Rtrim(Mailingstreet1)+Char(13)+Char(10) Else '' End+
Case When Rtrim(Isnull(Mailingstreet2,''))<>'' Then Rtrim(Mailingstreet2)+Char(13)+Char(10) Else '' End+
Rtrim(Isnull(Mailingsuburb,''))+' '+Rtrim(Isnull(Mailingstate,''))+' '+Rtrim(Isnull(Postcode,''))));
End

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenLongAddr]') AND type in (N'P', N'PC'))
DROP PROCEDURE GenLongAddr


Create and Drop Triggers

CREATE TRIGGER Upd_Locn_Qty ON PLOCDETAILS AFTER UPDATE AS
BEGIN
SET NOCOUNT ON; -- stops trigger from returning number of rows affected
Update Products Set
ZCity=(Select LCity from GW_Qty_By_Locn Where GW_Qty_By_Locn.ProductID=Products.UniqueID),
ZPooraka=(Select LPooraka from GW_Qty_By_Locn Where GW_Qty_By_Locn.ProductID=Products.UniqueID),
ZLonsdale=(Select LLonsdale from GW_Qty_By_Locn Where GW_Qty_By_Locn.ProductID=Products.UniqueID),
ZStMarys=(Select LStMarys from GW_Qty_By_Locn Where GW_Qty_By_Locn.ProductID=Products.UniqueID),
ZGawler=(Select LGawler from GW_Qty_By_Locn Where GW_Qty_By_Locn.ProductID=Products.UniqueID)
Where uniqueid=(Select productid from inserted)
END

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'Upd_Locn_Qty'))
DROP TRIGGER Upd_Locn_Qty


Database Schemas

Create View SchemaServer As
Select Cast(ServerProperty('ServerName') as Char(50)) as ServerName,
Cast(ServerProperty('FilestreamShareName') as Char(50)) as ServiceName,
case SubString(convert(char(10),ServerProperty('ProductVersion')),1,charindex('.',convert(char(10),ServerProperty('ProductVersion'))+'.')-1)
when '11' then 'SQL2011' when '10' then 'SQL2008' When '9' then 'SQL2005' When '8' then 'SQL2000' else 'Unknown' end as Vrsn,
Cast(ServerProperty('ProductLevel') as Char(10)) as SrvPack, Cast(ServerProperty('Edition') as Char(20)) as Edition,
Cast(ServerProperty('ProductVersion') as Char(20)) as SQLVrsn,
Cast(ServerProperty('Collation') as Char(20)) as Collation,
Cast(ServerProperty('ComputernamePhysicalnetbios') as Char(20)) as NetBIOSName,
case when ServerProperty('IsIntegratedSecurityOnly')=0 then 'MixedMode' else 'WindowsAuth' End as LoginMode,
case when ServerProperty('IsSingleuser')=0 then 'MultiUser' else 'SingleUser' end as UserMode,
Cast(ServerProperty('MachineName') as Char(20)) as MachineName,
Cast(IsNull(ServerProperty('NumLicenses'),0) as Int) as NumLicenses

Create View SchemaDatabases As
SELECT M2.Name as DatabaseName,
Max(dbID) as dbID, Max(CrDate) as CreateDate,
Max(Case When Type=0 then Physical_Name else '' end) as DataFileName,
Max(Case When Type=1 then Physical_Name else '' end) as LogFileName,
Max(Case When Type=0 then M1.Name else '' end) as LogicalDataName,
Max(Case When Type=1 then M1.Name else '' end) as LogicalLogName,
Max(DatabasePropertyEx(M2.Name, 'IsAutoClose')) as 'AutoClose',
Max(DatabasePropertyEx(M2.Name, 'IsAutoShrink')) As 'AutoShrink',
Max(DatabasePropertyEx(M2.Name, 'IsTornPageDetectionEnabled')) As 'TornPageDetect',
Max(DatabasePropertyEx(M2.Name, 'Recovery')) As 'Recovery',
Max(DatabasePropertyEx(M2.Name, 'Status')) As 'Status',
Max(DatabasePropertyEx(M2.Name, 'Updateability')) As 'Updateability',
Max(DatabasePropertyEx(M2.Name, 'UserAccess')) As 'UserAccess'
From sys.master_files M1
left outer join master.dbo.sysdatabases M2
On M2.dbid=M1.database_id
Where dbID > 4
Group By M2.Name

Create View SchemaTables As
SELECT T1.TABLE_CATALOG as TableCatalog,
T1.TABLE_SCHEMA as TableSchema,
SO.name as TableName,
replace(T1.TABLE_TYPE, 'BASE ', '') As TableType,
PS.row_count as RecordCount
From sys.Indexes SI
Inner JOIN sys.objects SO
On SI.OBJECT_ID=SO.OBJECT_ID
Inner JOIN sys.dm_db_partition_stats PS
On SI.OBJECT_ID=PS.OBJECT_ID
And SI.index_id=PS.index_id
Left Outer Join INFORMATION_SCHEMA.TABLES T1
on SO.Name=T1.Table_Name
Where SI.index_id<2 And SO.is_ms_shipped=0

Create View SchemaColumns As
Select ST.*,
Column_Name As ColumnName,
Data_type As DataType,
Isnull(Character_Maximum_Length,'0') As MaxLen,
Ordinal_position As Position,
Is_nullable As AllowNulls
From SchemaTables ST
Inner Join Information_schema.Columns ISC On ST.TableName=ISC.Table_name

Create View SchemaIndexes As
Select SO.name AS TableName,
SI.name AS IndexName,
SI.type_desc AS IndexType
From sys.indexes SI Inner Join sys.objects SO
On SI.object_id=SO.object_id
WHERE SO.type='U'
And SI.name IS NOT NULL

Create View SchemaViews As
Select TABLE_NAME as ViewName,
IS_UPDATABLE AS Updateable,
VIEW_DEFINITION as Definition
FROM Information_Schema.Views

Create View SchemaFunctions As
SELECT ROUTINE_NAME as FuncName,
ROUTINE_TYPE as FuncType,
Case When ROUTINE_TYPE='Procedure' then ''
When CharIndex('char',IsNull(DATA_TYPE,''))>0
then IsNull(DATA_TYPE,'')+' ('+rtrim(Cast(IsNull(CHARACTER_MAXIMUM_LENGTH,0) as Char(5)))+')'
When CharIndex('int',IsNull(DATA_TYPE,''))+CharIndex('date',IsNull(DATA_TYPE,''))+CharIndex('time',IsNull(DATA_TYPE,''))>0
then IsNull(DATA_TYPE,'')
else (IsNull(DATA_TYPE,'')+' ('+rtrim(Cast(IsNull(NUMERIC_PRECISION_RADIX,0) as Char(5)))+','+
rtrim(Cast(IsNull(NUMERIC_SCALE,0) as Char(5)))+')' ) END as Returns,
ltrim(rtrim(IsNull(ROUTINE_DEFINITION,''))) as Definition,
SQL_DATA_ACCESS as Effect
from Information_Schema.ROUTINES ISR

Create View SchemaTriggers As
Select [Table]=OBJECT_NAME(SO.parent_obj),
[Trigger]=SO.name,
[Type]=CASE WHEN
(Select cmptlevel From master.dbo.sysdatabases Where [name]=DB_NAME())=80 THEN
CASE WHEN OBJECTPROPERTY(SO.[id], 'ExecIsInsteadOfTrigger')=1 THEN 'Instead Of' ELSE 'After' END Else 'After' END,
[Insert]=CASE WHEN OBJECTPROPERTY(SO.[id], 'ExecIsInsertTrigger')=1 THEN 'Yes' ELSE 'No' END,
[Update]=CASE WHEN OBJECTPROPERTY(SO.[id], 'ExecIsUpdateTrigger')=1 THEN 'Yes' ELSE 'No' END,
[Delete]=CASE WHEN OBJECTPROPERTY(SO.[id], 'ExecIsDeleteTrigger')=1 THEN 'Yes' ELSE 'No' END,
[Enabled?]=CASE WHEN OBJECTPROPERTY(SO.[id], 'ExecIsTriggerDisabled')=0 THEN 'Enabled' ELSE 'Disabled' END
From SysObjects SO
WHERE OBJECTPROPERTY(SO.id, 'IsTrigger')=1
And OBJECTPROPERTY(SO.id, 'IsMSShipped')=0