Need a script to drop/re-create PK and Non-cluster

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Need a script to drop/re-create PK and Non-cluster

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2008-08-25 : 12:02:27

if objectproperty(object_id('employee'), 'istable') = 1
DROP TABLE employee
go

CREATE TABLE Employee
(
EmpId INT IDENTITY(1,1) NOT NULL,
dept VARCHAR(30) NULL,
emp_name VARCHAR(80) NULL,
salary FLOAT NULL
)

ALTER TABLE Employee
ADD CONSTRAINT XPKID PRIMARY KEY CLUSTERED (EmpId)
go

-- Non-clustered index.
CREATE NONCLUSTERED INDEX NCI_emp_name ON employee (emp_name)
CREATE NONCLUSTERED INDEX NCI_dept ON employee (dept)
go

Do you know where can I find a script to drop/re-create all PK and Non-clustered indexes. I have about 600 tables need to do. My Tech Lead doesn't
want to execute the statement below. I would guess it's the same by using drop/re-create them. Any helps is greatly appreciate.

ALTER INDEX NCI_emp_name ON Employee REBUILD
go

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-08-25 : 13:28:01

you should be able to generate one with SSMS. You may have to adjust the order of drops and creates to your liking, as the code generators tend to go alphabetically, rather than in order of dependency.