Script to Get Free Disk Space for SQL Server
Exec master.sys.xp_fixeddrives
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
This is a hidden stored procedure, this will be executed for each table in the database you connected.
Create table
CREATE TABLE [dbo].[Suppliers](
[SupplierID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL
CONSTRAINT [PK_Suppliers] PRIMARY KEY CLUSTERED
(
[SupplierID] ASC
)
)
INSERT INTO [Suppliers] VALUES('Exotic Liquids', 'Charlotte Cooper', 'Purchasing Manager', '49 Gilbert St.', 'London', null, 'EC1 4SD', 'UK');
INSERT INTO [Suppliers] VALUES('New Orleans Cajun Delights', 'Shelley Burke', 'Order Administrator', 'P.O. Box 78934', 'New Orleans', 'LA', '70117', 'USA');
INSERT INTO [Suppliers] VALUES('Tokyo Traders', 'Yoshi Nagase', 'Marketing Manager', '9-8 Sekimai Musashino-shi', 'Tokyo', null, '100', 'Japan');
INSERT INTO [Suppliers] VALUES('Pavlova, Ltd.', 'Ian Devling', 'Marketing Manager', '74 Rose St. Moonie Ponds', 'Melbourne', 'Victoria', '3058', 'Australia');
INSERT INTO [Suppliers] VALUES('Refrescos Americanas LTDA', 'Carlos Diaz', 'Marketing Manager', 'Av. das Americanas 12.890', 'Sao Paulo', null, '5442', 'Brazil');
Add some duplicate data
INSERT INTO [Suppliers] VALUES('Pavlova, Ltd.', 'Ian Devling', 'Marketing Manager', '74 Rose St. Moonie Ponds', 'Melbourne', 'Victoria', '3058', 'Australia');
INSERT INTO [Suppliers] VALUES('Refrescos Americanas LTDA', 'Carlos Diaz', 'Marketing Manager', 'Av. das Americanas 12.890', 'Sao Paulo', null, '5442', 'Brazil');
Find duplicate records
SELECT CompanyName, MAX(SupplierID) AS ID
FROM Suppliers
GROUP BY CompanyName
HAVING count(*) > 1
Remove duplicate records
DELETE a
FROM Suppliers a
JOIN
(
SELECT CompanyName, MAX(SupplierID) AS ID
FROM Suppliers
GROUP BY CompanyName
HAVING count(*) > 1
) b
ON a.CompanyName = b.CompanyName
AND a.SupplierID < b.ID
To check if a Stored Procedure exists, use query below
select * from sys.objects where type_desc = 'SQL_STORED_PROCEDURE' AND name = 'sp_UserInsert'
or
select * from sys.objects where type = 'p' AND name = 'sp_UserInsert'
or using object_id function
if object_id('sp_UserInsert', 'P') is null
Print 'Stored Procedure Does Not Exists'
ELSE
Print 'Stored Procedure Exists'
To check if a table exists, using OBJECT_ID function
IF OBJECT_ID ('Northwind.dbo.Categories','U') IS NULL
Print 'Table Does Not Exists'
ELSE
Print 'Table Exists'
or
select * from sysobjects where name='Categories' and xtype='U'
Create table if not exists
IF OBJECT_ID ('Northwind.dbo.Categories','U') IS NULL
create table Categories (
CategoryName varchar(64) not null
)
or
if not exists (select * from sysobjects where name='Categories' and xtype='U')
create table Categories (
CategoryName varchar(64) not null
)