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.|||I was working on some SQL scripts to do that, as well as droping and recreat
ing the FKeys.
I need to check how complete they are. I can let you have them, but I can't
guarrentee that they work 100%|||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.
>|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:unZqlpJDEHA.684@.tk2msftngp13.phx.gbl...
> 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.
I've made a few changes to your script to support a few extra features:
Fixed BUG with not showing NONCLUSTERED Primary Keys
Supports Table Owners other than [dbo]
Supports FileGroups
Supports DESC index columns
Supports FILLFACTOR & PAD_INDEX options
Changed order of index/constraint creation so that CLUSTERED is always first
Added [] around all object names
You could also add support for the IGNORE_DUP_KEY index option if you want.
CREATE PROC INDX_SCRIPING(
@.iTableName SYSNAME = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tableId INT
DECLARE @.indexId INT
DECLARE @.indexName SYSNAME
DECLARE @.tableOwner SYSNAME
DECLARE @.tableName SYSNAME
DECLARE @.groupName SYSNAME
DECLARE @.colId INT
DECLARE @.keyNo INT
DECLARE INDEX_CURSOR CURSOR FOR
SELECT I.ID,I.INDID,I.NAME,U.NAME,OBJECT_NAME(O.ID),F.GROUPNAME
FROM SYSINDEXES I
INNER JOIN SYSOBJECTS O ON I.ID = O.ID
INNER JOIN SYSFILEGROUPS F ON I.GROUPID=F.GROUPID
INNER JOIN SYSUSERS U ON O.UID=U.UID
WHERE O.XTYPE = 'U' AND ((@.iTableName IS NULL) OR (O.ID =
OBJECT_ID(@.iTableName)))
CREATE TABLE #indexInfo
(tableId INT,
indexId INT,
indexName SYSNAME,
tableOwner SYSNAME,
tableName SYSNAME,
groupname SYSNAME,
keys VARCHAR(6800),
isClustered BIT,
isPrimaryKey BIT,
IsUnique BIT,
IsUniqueCnst BIT,
IsPadIndex BIT,
indFillFactor int null)
OPEN INDEX_CURSOR
FETCH NEXT FROM INDEX_CURSOR
INTO @.tableId, @.indexId, @.indexName, @.tableOwner, @.tableName, @.groupName
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #indexInfo Values
(@.tableId,
@.indexId,
@.indexName,
@.tableOwner,
@.tableName,
@.groupName,
'',
CASE @.indexId
WHEN 1 then 1
ELSE 0
END,
ISNULL(OBJECTPROPERTY(object_id(@.indexNa
me),'IsPrimaryKey'),0),
ISNULL(INDEXPROPERTY(@.tableId, @.indexName,'IsUnique'),0),
ISNULL(OBJECTPROPERTY(object_id(@.indexNa
me),'IsUniqueCnst'),0),
ISNULL(INDEXPROPERTY(@.tableId, @.indexName,'IsPadIndex'),0),
INDEXPROPERTY(@.tableId, @.indexName,'IndexFillFactor') )
--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) + ']' +
CASE WHEN indexkey_property(@.tableId, @.indexId, @.keyNO,
N'isdescending') = 1 THEN ' DESC,' ELSE ',' END
WHERE tableId = @.tableId
AND indexId = @.indexId
FETCH NEXT FROM indexKey_Cursor
INTO @.colId,@.keyNO
END
CLOSE indexKey_Cursor
DEALLOCATE indexKey_Cursor
FETCH NEXT FROM INDEX_CURSOR
INTO @.tableId, @.indexId, @.indexName, @.tableOwner, @.tableName, @.groupName
END
CLOSE INDEX_CURSOR
DEALLOCATE INDEX_CURSOR
DELETE #indexInfo WHERE keys = ''
UPDATE #indexInfo SET keys = LEFT(keys,LEN(Keys)-1)
declare @.index table(pos smallint,sql varchar(8000))
-- Drop Primaty Key or Unique Constraint
INSERT INTO @.INDEX SELECT 1,'ALTER TABLE [' + tableOwner + '].[' + t
ablename
+ '] DROP CONSTRAINT [' + indexName + ']' +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isPrimarykey = 1 or IsUniqueCnst = 1
-- Drop Index
INSERT INTO @.INDEX SELECT 2,'DROP INDEX [' + tableOwner + '].[' + ta
blename
+ '].[' + indexName + ']' +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isPrimarykey = 0 and IsUniqueCnst = 0
-- Create Primary Key Constraint
INSERT INTO @.INDEX SELECT CASE WHEN isClustered = 1 THEN 3 ELSE 4 END,
'ALTER TABLE [' + tableOwner + '].[' + tableName + ']' +
' ADD CONSTRAINT [' + indexName + ']' +
' PRIMARY KEY' +
CASE WHEN isClustered = 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED'
END +
' (' + keys + ')' +
' ON [' + groupName + ']' +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isPrimarykey = 1
-- Create Unique Constraint
INSERT INTO @.INDEX SELECT CASE WHEN isClustered = 1 THEN 3 ELSE 4 END,
'ALTER TABLE [' + tableOwner + '].[' + tableName + ']' +
' ADD CONSTRAINT [' + indexName + ']' +
' UNIQUE' +
CASE WHEN isClustered = 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED'
END +
' (' + keys + ')' +
CASE WHEN indFillFactor is not null then ' WITH FILLFACTOR=' +
CAST(indFillFactor AS VARCHAR) ELSE '' END +
CASE WHEN (indFillFactor is not null AND IsPadIndex = 1 ) THEN ', PAD_INDEX'
ELSE '' END +
' ON [' + groupName + ']' +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isPrimarykey = 0 and IsUniqueCnst = 1 and IsUnique = 1
-- Create Index
INSERT INTO @.INDEX SELECT CASE WHEN isClustered = 1 THEN 3 ELSE 5 END,
'CREATE' +
CASE WHEN IsUnique = 1 THEN ' UNIQUE' ELSE '' END +
CASE WHEN isClustered = 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED'
END +
' INDEX [' + indexName + '] ON [' + tableOwner + '].[' +
tableName + ']' +
'(' + keys + ')' +
CASE WHEN indFillFactor is not null then ' WITH FILLFACTOR=' +
CAST(indFillFactor AS VARCHAR) ELSE '' END +
CASE WHEN (indFillFactor is not null AND IsPadIndex = 1 ) THEN ', PAD_INDEX'
ELSE '' END +
' ON [' + groupName + ']' +
CHAR(13) + 'GO'
FROM #indexInfo
WHERE isPrimarykey = 0 and IsUniqueCnst = 0
TRUNCATE TABLE #indexInfo
DROP TABLE #indexInfo
SELECT sql FROM @.INDEX ORDER BY pos
END