cannot script ONLY indexes

hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
and keys defaults and contraints, i do NOT want to generate anything else as
these already exist in the destination database that im going to run the
script on. what options will i need to tick on the "general" tab in order
for sql to generate the script correctly?, i have tried scripting all
objects and then unticking the create and drop objects commands, but this
then creates an empty script, i have also tried leaving the create and drop
objects commands ticked, but this does not seem to script any of the
existing indexes, anybody have a clue as to how i get around this?.
many thanks!,
paul.Hi Paul,
You need to select to script all the tables, and all indexes and
constraints, with the create statements only. This will generate a script
with everything you want, plus create scripts for all the table. The create
statements for all the tables will be first in the script, and all the
indexes and constraints will be in the script after that, so you can easily
delete the create statements for all the tables from the script.
Jacco Schalkwijk
SQL Server MVP
"Paul Eccleston" <paul.eccleston@.get-rid-of-this-bell-centres.com> wrote in
message news:40587e3b_1@.nnrp1.news.uk.psi.net...
> hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
> and keys defaults and contraints, i do NOT want to generate anything else
as
> these already exist in the destination database that im going to run the
> script on. what options will i need to tick on the "general" tab in order
> for sql to generate the script correctly?, i have tried scripting all
> objects and then unticking the create and drop objects commands, but this
> then creates an empty script, i have also tried leaving the create and
drop
> objects commands ticked, but this does not seem to script any of the
> existing indexes, anybody have a clue as to how i get around this?.
> many thanks!,
> paul.
>|||Just to add, you might want to make use of SQL DMO object model. This gives
you ultimate flexibility.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Paul Eccleston" <paul.eccleston@.get-rid-of-this-bell-centres.com> wrote in
message news:40587e3b_1@.nnrp1.news.uk.psi.net...
hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
and keys defaults and contraints, i do NOT want to generate anything else as
these already exist in the destination database that im going to run the
script on. what options will i need to tick on the "general" tab in order
for sql to generate the script correctly?, i have tried scripting all
objects and then unticking the create and drop objects commands, but this
then creates an empty script, i have also tried leaving the create and drop
objects commands ticked, but this does not seem to script any of the
existing indexes, anybody have a clue as to how i get around this?.
many thanks!,
paul.|||Hi ,
I have Wrote a vry good utility to script the Indexes (Drop and Create). Use
this script and put in your suggestions, so that I can enhance the script
before publishing.
This will consider the following,
1. CLustered Index
2. Primary key clusterd index
3. Primary key non clustered index
4. Unique CLsutered
5. Unique non clustered
6. Clusterd Index
Attached the script , I will be publishing this in MSDB India site shortly
with a document.
CREATE PROC INDX_SCRIPING(
@.iTableName SYSNAME = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tableId INT
DECLARE @.indexId INT
DECLARE @.indexName SYSNAME
DECLARE @.tableName SYSNAME
DECLARE @.colId INT
DECLARE @.keyNo INT
DECLARE INDEX_CURSOR CURSOR FOR
SELECT I.ID,I.INDID,I.NAME, OBJECT_NAME(O.ID)
FROM SYSINDEXES I
INNER JOIN SYSOBJECTS O
ON I.ID = O.ID
WHERE O.XTYPE = 'U'
AND ((@.iTableName IS NULL) OR (O.ID = OBJECT_ID(@.iTableName)))
CREATE TABLE #indexInfo
(tableId INT,
indexId INT,
indexName SYSNAME,
tableName SYSNAME,
keys VARCHAR(7000),
isClustered BIT,
isPrimaryKey BIT,
IsUnique BIT,
IsUniqueCnst BIT)
OPEN INDEX_CURSOR
FETCH NEXT FROM INDEX_CURSOR
INTO @.tableId, @.indexId, @.indexName, @.tableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #indexInfo Values
(@.tableId,
@.indexId,
@.indexName,
@.tableName,
'',
CASE @.indexId
WHEN 1 then 1
ELSE 0
END,
ISNULL(OBJECTPROPERTY(object_id(@.indexNa
me),'IsPrimaryKey'),0),
ISNULL(INDEXPROPERTY(OBJECT_ID(@.tableNam
e), @.indexName,'IsUnique'),0),
ISNULL(OBJECTPROPERTY(object_id(@.indexNa
me),'IsUniqueCnst'),0))
--PRINT 'Table ' + @.tableName
DECLARE indexKey_Cursor CURSOR FOR
SELECT colid,KeyNo
FROM SYSINDEXKEYS
WHERE ID = @.tableId
AND INDID = @.indexId
OPEN indexKey_Cursor
FETCH NEXT FROM indexKey_Cursor
INTO @.colId,@.keyNO
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE #indexInfo SET
keys = keys + COL_NAME(@.tableid,@.colId) + ','
WHERE tableId = @.tableId
AND indexId = @.indexId
--PRINT 'COL ' + COL_NAME(@.tableid,@.colId)
FETCH NEXT FROM indexKey_Cursor
INTO @.colId,@.keyNO
END
CLOSE indexKey_Cursor
DEALLOCATE indexKey_Cursor
FETCH NEXT FROM INDEX_CURSOR
INTO @.tableId, @.indexId, @.indexName, @.tableName
END
DELETE #indexInfo WHERE keys = ''
UPDATE #indexInfo SET keys = LEFT(keys,LEN(Keys)-1)
declare @.index table(sql varchar(8000))
--Drop clustered primaty key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 1
-- Drop unique Constraint
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
-- Drop unique Constraint
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+ tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
--Drop clustered index
INSERT INTO @.INDEX SELECT 'DROP INDEX' + tablename+'.'+indexName + CHAR(13)
+ 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
--Drop non clustered index
INSERT INTO @.INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
--Drop unique Clustred Index
INSERT INTO @.INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
--Drop unique nonClustred Index
INSERT INTO @.INDEX SELECT 'DROP INDEX ' + tablename+'.' + indexName +
CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
--Drop non clustered primary key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE '+tablename + ' DROP CONSTRAINT ' +
indexName +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 0
-- Create Index Statement
-- Create Clustered Primary key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT PK_' + tableName +
' PRIMARY KEY CLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 1
-- Create Unique nonClustered index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT ' + indexName +
' UNIQUE NONCLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
-- Create Unique Clustered index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT ' + indexName +
' UNIQUE CLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 1 and IsUnique = 1
-- Create NonClustered Primary key index
INSERT INTO @.INDEX SELECT 'ALTER TABLE ' + tableName +
' ADD CONSTRAINT PK_' + tableName +
' PRIMARY KEY NONCLUSTERED (' + keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo WHERE isClustered = 0 and isPrimarykey = 1
and IsUniqueCnst = 0 and IsUnique = 0
--Create NonClustered Index
INSERT INTO @.INDEX SELECT 'CREATE NONCLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
--Create Clustered index
INSERT INTO @.INDEX SELECT 'CREATE CLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 0
-- Create Unique Clustered Index
INSERT INTO @.INDEX SELECT 'CREATE UNIQUE CLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 1 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
-- Create Unique nonClustered Index
INSERT INTO @.INDEX SELECT 'CREATE UNIQUE NONCLUSTERED INDEX '
+ indexName + ' ON '
+ tableName + '('
+ keys + ')' + CHAR(13) + 'GO'
FROM #indexInfo
WHERE isClustered = 0 and isPrimarykey = 0
and IsUniqueCnst = 0 and IsUnique = 1
TRUNCATE TABLE #indexInfo
DROP TABLE #indexInfo
CLOSE INDEX_CURSOR
DEALLOCATE INDEX_CURSOR
SELECT * FROM @.INDEX
END
How to execute
RECREATE_INDEX 'TEST_INDEX'
If you not giving the table name the script will generated for all user
tables.
Thanks
Hari
MCDBA
"Paul Eccleston" <paul.eccleston@.get-rid-of-this-bell-centres.com> wrote in
message news:40587e3b_1@.nnrp1.news.uk.psi.net...
> hi i am trying to use "Generate SQL Script" to script out ONLY the indexes
> and keys defaults and contraints, i do NOT want to generate anything else
as
> these already exist in the destination database that im going to run the
> script on. what options will i need to tick on the "general" tab in order
> for sql to generate the script correctly?, i have tried scripting all
> objects and then unticking the create and drop objects commands, but this
> then creates an empty script, i have also tried leaving the create and
drop
> objects commands ticked, but this does not seem to script any of the
> existing indexes, anybody have a clue as to how i get around this?.
> many thanks!,
> paul.
>