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
 General SQL Server Forums
 Script Library
 Scripting PK+FK+Index

Author  Topic 

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-28 : 13:29:45
Hi,

Does anyone have scripts that generate the following:
1. All PK
2. All FK
3. All Indexes

Separate from the table DDL.
( EM can generate but with table DDL as well )

rockmoose

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-28 : 13:57:43
Just posted it:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41896

Could probably use some improvement.
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-28 : 14:09:00
Many thanx, appreciated !!!

If I find room for any improvement i'll post it.

Indexes anyone ?

rockmoose
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-28 : 16:36:21
Well enterprise manager will do indexes.
It will also do primry and foreign keys without the table ddl but with defaults and check constraints.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-29 : 07:46:37
Hmmmmm,
I have used that dialog hundreds of times and It just never ever!!!
occurred to me that you could uncheck both the CREATE and DROP checkboxes.

Sometimes you just can't see what is right in front of your eyes
Thanx

rockmoose
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 07:47:27
Can't find their names on Google, so I can't say where I got this from I'm afraid.

Untested!

Edit: Found a link, but its not live anymore http://www.swynk.com/downloads/sp_IndexKeyText.sql

Kristen

/*
*** Karl Mortimer, John Thorpe
*/
-- Tested on SQL SERVER 7.0 & 2000
-- Supports upto 16 column compound keys
--
-- procedure to create 'create index...' statements and 'drop index...' statements for a given table
-- option to include or exclude Primary keys and create 'alter table...' statements
-- option to include or exclude Foreign Keys and create 'alter table...' statements
-- excludes unique constraints (as these do not include indexing) and will not include
-- sorted_data and sorted_data_reorg parameters to the statement
-- as they must be specified manually. This means that if data needs
-- to be sorted when the index is put on, a dbcc reindex must be
-- executed for that table afterwards with the sorted_data / sorted_data_reorg
-- parameters correctly set
--
if exists (select * from sysobjects where id = object_id('dbo.sp_IndexKeyText') and sysstat & 0xf = 4)
drop procedure dbo.sp_IndexKeyText
GO

create procedure sp_IndexKeyText

@Options tinyint = 25, -- bitmap of options. First bit determines whether indexes are included
-- Second bit determines whether primary keys are included
-- Third bit determines whether foreign keys are included
-- Fourth bit determines whether drop statements are scripted
-- Fifth bit determines whether create statements are scripted
-- Default bit 1 true for indexes only, bits 4 and 5 true for both to be scripted.
@Tablename VARCHAR(50) = NULL --optional tablename to drop indexes on a given table


as

set nocount on

if @Options & 8 <> 8 AND @Options & 16 <> 16 --Both Create and Drop script options turned OFF
begin
RAISERROR('Both drop and create scripting switched off, no output would be produced',10,1)
RETURN
end

if @options = 0
begin
RAISERROR('All Index options turned off - no output will be produced',10,1)
RETURN
end

if @Tablename is not null
and @Tablename not in (select name from sysobjects where type = 'U')
begin
RAISERROR('Invalid value given for @Tablename - table does not exist in current database',10,1)
RETURN
end

declare @objid int /* the object id of the table */
declare @indid int /* the index id of an index */
declare @keys varchar(200) /* string to build up index key in */
declare @inddesc varchar(68) /* string to build up index desc in */
declare @dbname varchar(30),
@primary BIT,
@unique BIT,
@clustered BIT,
@fillfactor TINYINT,
@ignoredupkey BIT,
@allowduprow BIT,
@ignoreduprow BIT,
@location sysname,
@index_name VARCHAR(30),
@with VARCHAR(100),
@IndexString VARCHAR(255),
@objname VARCHAR(50),
@Title varchar(30),
@empty varchar(1),
@groupid smallint,
@ExecString varchar(200)

select @empty = '' -- Empty string for concatenation

create table #sysfilegroups(
groupid smallint not null,
allocpolicy smallint not null,
status int not null,
groupname sysname not null)

select @ExecString = 'select * from '+DB_NAME(0)+'..sysfilegroups'

insert into #sysfilegroups
exec (@ExecString)

IF @Tablename is NULL
BEGIN

DECLARE CurTables CURSOR FOR
SELECT name
FROM sysobjects
WHERE type = 'U'
ORDER BY name

END
ELSE
BEGIN

DECLARE CurTables CURSOR FOR
SELECT name
FROM sysobjects
WHERE type = 'U'
AND name = @Tablename
ORDER BY name

END

OPEN CurTables
FETCH NEXT FROM CurTables INTO @objname

CREATE TABLE #CREATE (description VARCHAR(255))

CREATE TABLE #DROP (description VARCHAR(255))

WHILE @@FETCH_STATUS = 0
BEGIN
/*
** Check to see the the table exists and initialize @objid.
*/
select @objid = object_id(@objname)


/*
** Table doesn't exist so return.
*/
if @objid is NULL
begin
GOTO NEXT_TABLE
end


/*
** See if the object has any indexes.
** Since there may be more than one entry in sysindexes for the object,
** this select will set @indid to the index id of the first index.
*/
select @indid = min(indid) from sysindexes
where id = @objid
and indid > 0
and indid < 255

/*
** If no indexes, return.
*/
if @indid is NULL
begin
GOTO NEXT_TABLE
end


set nocount on

/*
** Now check out each index and figure out its type and keys
*/

while @indid IS NOT NULL
begin
select @primary = 0

/*
** See if the index is primary key constraint (0x800).
*/

if exists (select *
from master.dbo.spt_values v, sysindexes i
where i.status & v.number = v.number
and v.type = 'I'
and v.number = 2048
and i.id = @objid
and i.indid = @indid)
BEGIN
if @Options & 2 = 2 -- Primary Key Flag Set
-- if primary key, set flag
select @primary = 1
else GOTO Ignore_index
END

if @primary = 0 and @Options & 1 <> 1 GOTO Ignore_index

/*
** See if the index is unique key constraint (0x1000).
*/
if exists (select * from master.dbo.spt_values v, sysindexes i
where i.status & v.number = v.number
and v.type = 'I'
and v.number = 4096
and i.id = @objid
and i.indid = @indid)
BEGIN
-- if Unique key, ignore index
GOTO Ignore_index
END


/*
** First we'll figure out what the keys are.
*/
declare @k int
declare @thiskey varchar(30)
declare @lastindid int

select @k = 1

while @k <= 16
begin
select @thiskey = index_col(@objname, @indid, @k)

if @thiskey is null
goto keysdone

if @k = 1
select @keys = '['+index_col(@objname, @indid, @k)+']'
else
select @keys = @keys + ', ['+index_col(@objname, @indid, @k)+']'

/*
** Increment @k so it will check for the next key.
*/
select @k = @k + 1

end


/*
** When we get here we now have all the keys.
*/
keysdone:

/*
** Initialize the index description by figuring out if it's a
** clustered or nonclustered index.
*/
if @indid = 1
select @clustered = 1 -- clustered

if @indid > 1
select @clustered = 0 -- non-clustered

/*
** Now we'll check out the status bits for this index and
** build an english description from them.
*/

/*
** See if the index is ignore_dupkey (0x01).
*/
if exists (select *
from master.dbo.spt_values v, sysindexes i
where i.status & v.number = v.number
and v.type = 'I'
and v.number = 1
and i.id = @objid
and i.indid = @indid)
BEGIN
SELECT @ignoredupkey = 1
END
else
BEGIN
SELECT @ignoredupkey = 0
END

/*
** See if the index is unique (0x02).
*/
if exists (select *
from master.dbo.spt_values v, sysindexes i
where i.status & v.number = v.number
and v.type = 'I'
and v.number = 2
and i.id = @objid
and i.indid = @indid)
BEGIN
SELECT @unique = 1
END
else
BEGIN
SELECT @unique = 0
END

/*
** Add the location of the data.
*/

select @location = fg.groupname from #sysfilegroups fg, sysindexes i
where fg.groupid = i.groupid
and i.id = @objid
and i.indid = @indid

-- get the index_name and fillfactor

select @index_name = name,
@fillfactor = origfillfactor
from sysindexes
where id = @objid
and indid = @indid

-- set with clause
SELECT @with = NULLIF('FILLFACTOR = '+CONVERT(VARCHAR,@fillfactor)+',', 'FILLFACTOR = 0,')

select @with = case when @with is null then @empty else @with end
+CASE @ignoredupkey
WHEN 1 THEN 'IGNORE_DUP_KEY '
WHEN 0 THEN @empty
END

-- if @with clause contains anything add keyword 'WITH' to beginning of string

IF @with <> @empty
BEGIN
SELECT @with = ' WITH '+@with
END

if @primary = 0
begin
select @IndexString = NULLIF(' ON '+@Location, ' ON PRIMARY')



select @IndexString = case when @IndexString is null then @empty else @IndexString end

-- print create index statement out
SELECT @IndexString =
'CREATE '+CASE @unique
WHEN 1 THEN 'UNIQUE '
WHEN 0 THEN @empty
END
+CASE @clustered
WHEN 1 THEN ' CLUSTERED '
WHEN 0 THEN ' NONCLUSTERED '
END
+' INDEX ['+@index_name +'] '
+'ON ['+@objname+'] ('+@keys+')'
+case when @with is null then @empty else @with end
+@Indexstring
end
else
begin
select @IndexString = NULLIF('ON '+@Location, 'ON PRIMARY')

select @IndexString = case when @IndexString is null then @empty else @IndexString end

-- print alter table statement out
select @IndexString =
'ALTER TABLE ['+@objname+'] ADD CONSTRAINT ['+@index_name+'] PRIMARY KEY '
+CASE @clustered
WHEN 1 THEN 'CLUSTERED '
WHEN 0 THEN 'NONCLUSTERED '
END
+'('+@keys+')'
+case when @with is null then @empty else @with end
+@IndexString
end

IF @Options & 16 = 16 --create flag ON
BEGIN
SELECT @IndexString = @IndexString + CHAR(13)+'GO'+CHAR(13)+CHAR(13)

INSERT INTO #CREATE(description) VALUES (@IndexString)
END

IF @Options & 8 = 8 --drop flag ON
BEGIN
if @primary = 0
SELECT @IndexString = 'DROP index ['+@objname+'].['+@index_name+']'
else
SELECT @IndexString = 'ALTER TABLE ['+@objname+'] DROP CONSTRAINT ['+@index_name+']'

SELECT @IndexString = @IndexString + CHAR(13)+'GO'+CHAR(13)+CHAR(13)

INSERT INTO #DROP(description) VALUES (@IndexString)
END

ignore_index:


/*
** Now move @indid to the next index.
*/
select @lastindid = @indid
select @indid = NULL
select @indid = min(indid)
from sysindexes
where id = @objid
and indid > @lastindid
and indid < 255
end


NEXT_TABLE:

FETCH NEXT FROM CurTables INTO @objname

END -- WHILE loop

/*
** Now print out the contents of the temporary index table.
*/
SET nocount on
IF @Options & 8 = 8 --drop flag ON
begin
if @Options & 2 = 2 AND @Options & 1 = 1 SELECT description AS 'Index / Primary Key Drops' FROM #drop
if @Options & 2 <> 2 AND @Options & 1 = 1 SELECT description AS 'Index Drops' FROM #drop
if @Options & 2 = 2 AND @Options & 1 <> 1 SELECT description AS 'Primary Key Drops' FROM #drop
end

IF @Options & 16 = 16 --create flag ON
begin
if @Options & 2 = 2 AND @Options & 1 = 1 SELECT description AS 'Index / Primary Key Creates' FROM #create
if @Options & 2 <> 2 AND @Options & 1 = 1 SELECT description AS 'Index Creates' FROM #create
if @Options & 2 = 2 AND @Options & 1 <> 1 SELECT description AS 'Primary Key Creates' FROM #create
end

CLOSE CurTables
DEALLOCATE CurTables

drop table #create
drop table #drop

set nocount off

/*
*** Foreign Key code generation
*/

IF @Options & 4 = 4 -- Foreign Key flag ON
begin

SET nocount on
IF @Options & 8 = 8 --drop flag ON
begin
select 'Foreign Key drops' = 'alter table ['+object_name(fkeyid)+']'+char(13)
+'drop constraint ['+object_name(constid)+']'+char(13)+char(13)+'GO'+char(13)+char(13)
from sysreferences
where OBJECT_NAME(rkeyid) like isnull(@Tablename,'%')
order by object_name(fkeyid)
end

IF @Options & 16 = 16 --create flag ON
begin
select 'Foreign Key creates' = 'alter table ['+object_name(fkeyid)+'] '+char(13)
+'add constraint ['+object_name(constid)+'] '
+'Foreign key(['+f1.name+']'
+case when f2.name is null then @empty else '['+f2.name+']' end
+case when f3.name is null then @empty else '['+f3.name+']' end
+case when f4.name is null then @empty else '['+f4.name+']' end
+case when f5.name is null then @empty else '['+f5.name+']' end
+case when f6.name is null then @empty else '['+f6.name+']' end
+case when f7.name is null then @empty else '['+f7.name+']' end
+case when f8.name is null then @empty else '['+f8.name+']' end
+case when f9.name is null then @empty else '['+f9.name+']' end
+case when f10.name is null then @empty else '['+f10.name+']' end
+case when f11.name is null then @empty else '['+f11.name+']' end
+case when f12.name is null then @empty else '['+f12.name+']' end
+case when f13.name is null then @empty else '['+f13.name+']' end
+case when f14.name is null then @empty else '['+f14.name+']' end
+case when f15.name is null then @empty else '['+f15.name+']' end
+case when f16.name is null then @empty else '['+f16.name+']' end
+')'+char(13)
+'references ['+object_name(rkeyid)+'] (['+c1.name+']'
+case when c2.name is null then @empty else '['+c2.name+']' end
+case when c3.name is null then @empty else '['+c3.name+']' end
+case when c4.name is null then @empty else '['+c4.name+']' end
+case when c5.name is null then @empty else '['+c5.name+']' end
+case when c6.name is null then @empty else '['+c6.name+']' end
+case when c7.name is null then @empty else '['+c7.name+']' end
+case when c8.name is null then @empty else '['+c8.name+']' end
+case when c9.name is null then @empty else '['+c9.name+']' end
+case when c10.name is null then @empty else '['+c10.name+']' end
+case when c11.name is null then @empty else '['+c11.name+']' end
+case when c12.name is null then @empty else '['+c12.name+']' end
+case when c13.name is null then @empty else '['+c13.name+']' end
+case when c14.name is null then @empty else '['+c14.name+']' end
+case when c15.name is null then @empty else '['+c15.name+']' end
+case when c16.name is null then @empty else '['+c16.name+']' end
+')'+char(13)
+char(13)
from sysreferences,
syscolumns c1,
syscolumns c2,
syscolumns c3,
syscolumns c4,
syscolumns c5,
syscolumns c6,
syscolumns c7,
syscolumns c8,
syscolumns c9,
syscolumns c10,
syscolumns c11,
syscolumns c12,
syscolumns c13,
syscolumns c14,
syscolumns c15,
syscolumns c16,
syscolumns f1,
syscolumns f2,
syscolumns f3,
syscolumns f4,
syscolumns f5,
syscolumns f6,
syscolumns f7,
syscolumns f8,
syscolumns f9,
syscolumns f10,
syscolumns f11,
syscolumns f12,
syscolumns f13,
syscolumns f14,
syscolumns f15,
syscolumns f16
WHERE c1.colid =* rkey1
AND c1.id =* rkeyid
AND c2.colid =* rkey2
AND c2.id =* rkeyid
AND c3.colid =* rkey3
AND c3.id =* rkeyid
AND c4.colid =* rkey4
AND c4.id =* rkeyid
AND c5.colid =* rkey5
AND c5.id =* rkeyid
AND c6.colid =* rkey6
AND c6.id =* rkeyid
AND c7.colid =* rkey7
AND c7.id =* rkeyid
AND c8.colid =* rkey8
AND c8.id =* rkeyid
AND c9.colid =* rkey9
AND c9.id =* rkeyid
AND c10.colid =* rkey10
AND c10.id =* rkeyid
AND c11.colid =* rkey11
AND c11.id =* rkeyid
AND c12.colid =* rkey12
AND c12.id =* rkeyid
AND c13.colid =* rkey13
AND c13.id =* rkeyid
AND c14.colid =* rkey14
AND c14.id =* rkeyid
AND c15.colid =* rkey15
AND c15.id =* rkeyid
AND c16.colid =* rkey16
AND c16.id =* rkeyid
AND f1.colid =* fkey1
AND f1.id =* fkeyid
AND f2.colid =* fkey2
AND f2.id =* fkeyid
AND f3.colid =* fkey3
AND f3.id =* fkeyid
AND f4.colid =* fkey4
AND f4.id =* fkeyid
AND f5.colid =* fkey5
AND f5.id =* fkeyid
AND f6.colid =* fkey6
AND f6.id =* fkeyid
AND f7.colid =* fkey7
AND f7.id =* fkeyid
AND f8.colid =* fkey8
AND f8.id =* fkeyid
AND f9.colid =* fkey9
AND f9.id =* fkeyid
AND f10.colid =* fkey10
AND f10.id =* fkeyid
AND f11.colid =* fkey11
AND f11.id =* fkeyid
AND f12.colid =* fkey12
AND f12.id =* fkeyid
AND f13.colid =* fkey13
AND f13.id =* fkeyid
AND f14.colid =* fkey14
AND f14.id =* fkeyid
AND f15.colid =* fkey15
AND f15.id =* fkeyid
AND f16.colid =* fkey16
AND f16.id =* fkeyid
AND OBJECT_NAME(rkeyid) like isnull(@Tablename,'%')
end

SET nocount off
end

drop table #sysfilegroups

set nocount off

return

go
Go to Top of Page
   

- Advertisement -