How to create Database:
CREATE DATABASE [Source]
OR
CREATE DATABASE [neuSource] ON PRIMARY
(
NAME = 'neuSource',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Source.mdf' ,
SIZE = 1280KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
( NAME = 'neuSource_log',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Source_log.LDF' ,
SIZE = 504KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%
)
GO
How to see databases:
Use master
Go
Select * from Sysdatabases
How to create Table
CREATE TABLE [dbo].[Audit](
[AuditKey] [int] IDENTITY(100,10) NOT NULL,
[TableProcessKey] [int] NOT NULL,
[PackageBranchName] [varchar](50) NOT NULL,
[PackageBranchRowCount] [int] NULL,
CONSTRAINT [Audit_PK] PRIMARY KEY CLUSTERED
)
GO
Create Table Statement to create Foreign Key (Column Level )
USE AdventureWorks2008
GO
CREATE TABLE ProductSales
(
SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY,
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
);
GO
Table Level
CREATE TABLE ProductSales
(
SalesID INT,
ProductID INT,
SalesPerson VARCHAR(25)
CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),
CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
);
GO
Alter Table Statement to create Foreign Key
ALTER TABLE ProductSales
ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
GO
Alter Table Statement to Drop Foreign Key
ALTER TABLE ProductSales
DROP CONSTRAINT fk_productSales_pid;
GO
How to create duplicate table without records
--select * into NewTable from OldTable where 1 = 2
OR
select top 0 * into NewTable from OldTable
Create Table Statement to create Primary Key (Column Level )
USE AdventureWorks2008
GO
CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
ProductName VARCHAR(25)
);
GO
Table Level
CREATE TABLE Products
(
ProductID INT,
ProductName VARCHAR(25)
CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
);
GO
-- Alter Table Statement to create Primary Key
ALTER TABLE Products
ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
GO
-- Alter Statement to Drop Primary key
ALTER TABLE Products
DROP CONSTRAINT pk_products_pid;
GO
How to check table is existing or not?
IF exists (select * from sys.objects where type= 'u' and name = 'Audit')
Print 'Table Already Exists'
Else
Print 'Need to create this table'
Go
How to check Procedure is existing or not?
IF NOT exists (select 1 from sys.objects where type= 'p' and name = 'TestSP')
Print 'Procedure Already Exists'
Else
CREATE PROCEDURE TestSP
@LastName nvarchar(50) = NULL,
@FirstName nvarchar(50)= NULL
AS
SELECT * FROM dbo.ETLConfiguration
WHERE ConfigurationFilter LIKE @FirstName
AND ConfigurationFilter LIKE @LastName;
GO
How to check Function is existing or not?
IF Not exists (select 1 from sys.objects where type= 'fn' and name = 'TestFunc')
Print 'Function does not Exists'
GO
CREATE FUNCTION dbo.TestFunc
-- Input dimensions in centimeters.
(@CubeLength decimal(4,1),
@CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END;
GO
Few most frequently used commands:
SP_HELPDB
SP_Help
SP_HelpText ProcedureName/Functionmname
SP_WHO
SP_WHO2
SP_Depends TableName/StoredPRocedure
--Rename Table
EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr','Table';
GO
--Renaming a column
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO
--Renaming an index
EXEC sp_rename 'Purchasing.ProductVendor.IX_ProductVendor_VendorID', 'IX_VendorID', 'INDEX';
GO
--Renaming an alias data type
EXEC sp_rename 'Phone', 'Telephone', 'USERDATATYPE';
GO
Common Functions what we used on regularly basis:
Sum()
Getdate()
ISNULL(A,B)
Convert()
Cast()
Max()
Min()
DatePart(d,-2,Getdate())
NULLIF()
Round()
Left()
Right()
