scripting primary and foreign keys
-
hi everyone,
recently we had to move data from a live sql 2005 server to a dev sql 2000 server. this was a bit of a process with 2005 back-ups not being able to restore on 2000.
we had to use alot of .csv's and flat files. but to do this we had to delete all the primary and foreign keys. this is so we would still have the correct relationships.
the problem is i now have to create all the keys again. is there an wasy way to script these in an alter table script for the whole db? with either 2000 or 2005.
-
Hi there,
If you are doing this regularly you might want to look at using a tool such as Red Gate's SQL Compare [Schema comparison and migration] and SQL Data Compare [Data comparison and migration] (there are 14 day free trials availible and other vendors also do similar tools) which will generate all of the scripts for moving schema and data over for you taking into account dependencies between objects etc.
Otherwise youll need to grab the alter table syntax out of management studio for each object and patch up as needed so that it works.
- James
--
James Moore
Red Gate Software Ltd -
if you have access to the original db on 2005, you can use the scripts below ; here you go:
first script generates both drop and add foreign key statments;
second script does PK,unique AND regular indexes.
DECLARE @QUERY VARCHAR(8000),
@DBNAME VARCHAR(128)
set @DBNAME=db_name()
SET @QUERY =
'USE ' + @DBNAME + '
SELECT CAST(F.NAME AS VARCHAR(255)) AS ForeignKeyName,
CAST(c.name as varchar(255)) AS ForeignTable,
CAST(fc.name as varchar(255)) AS ForeignColumn,
cast(fc2.name as varchar(255)) as ForeignColumn2,
CAST(p.name as varchar(255)) AS PrimaryTable,
CAST(rc.name as varchar(255)) AS PrimaryColumn,
cast(rc2.name as varchar(255)) as PrimaryColumn2
INTO #GetFKConstraints
FROM SYSOBJECTS F
INNER JOIN SYSOBJECTS C ON F.PARENT_OBJ = C.ID
INNER JOIN SYSREFERENCES R on F.ID = R.CONSTID
INNER JOIN SYSOBJECTS P ON R.RKEYID = P.ID
INNER JOIN SYSCOLUMNS RC ON R.RKEYID = RC.ID AND R.RKEY1 = RC.COLID
INNER JOIN SYSCOLUMNS FC ON R.FKEYID = FC.ID AND R.FKEY1 = FC.COLID
left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
WHERE F.TYPE = '+'''F'''+'
--SELECT * from #GetFKConstraints'+'
SELECT ' + '''ALTER TABLE ''' + '+ ForeignTable + ' + '''
DROP CONSTRAINT ''' + '+ ForeignKeyName
FROM #GetFKConstraints
ORDER BY ForeignTable'
+'
SELECT ' + '''ALTER TABLE ''' + '+ ForeignTable +' + '''
ADD CONSTRAINT ''' + '+ ForeignKeyName +'
+ ''' FOREIGN KEY ('''+ ' + ForeignColumn + '+ ''')
REFERENCES ''' + '+ PrimaryTable + ' + '''(''' + '+ PrimaryColumn + '+ ''')''' +
'FROM #GetFKConstraints
ORDER BY ForeignTable
'
EXEC (@QUERY)
GO
SELECT TOP 100
REPLICATE(' ',4000) AS COLNAMES ,
OBJECT_NAME(I.ID) AS TABLENAME,
I.ID AS TABLEID,
I.INDID AS INDEXID,
I.NAME AS INDEXNAME,
I.STATUS,
INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,
INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,
INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR
INTO #TMP
FROM SYSINDEXES I
WHERE I.INDID > 0
AND I.INDID < 255
AND (I.STATUS & 64)=0
--uncomment below to eliminate PK or UNIQUE indexes;
--what i call 'normal' indexes
--AND INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') =0
--AND INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') =0
DECLARE
@ISQL VARCHAR(4000),
@TABLEID INT,
@INDEXID INT,
@MAXTABLELENGTH INT,
@MAXINDEXLENGTH INT
--USED FOR FORMATTING ONLY
SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP
SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP
DECLARE C1 CURSOR FOR
SELECT TABLEID,INDEXID FROM #TMP
OPEN C1
FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @ISQL = ''
SELECT @ISQL=@ISQL + ISNULL(SYSCOLUMNS.NAME,'') + ',' FROM SYSINDEXES I
INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
WHERE I.INDID > 0
AND I.INDID < 255
AND (I.STATUS & 64)=0
AND I.ID=@TABLEID AND I.INDID=@INDEXID
ORDER BY SYSCOLUMNS.COLID
UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID
FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
END
CLOSE C1
DEALLOCATE C1
--AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA
UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)
SELECT 'CREATE '
+ CASE WHEN ISUNIQUE = 1 THEN ' UNIQUE ' ELSE ' ' END
+ CASE WHEN ISCLUSTERED = 1 THEN ' CLUSTERED ' ELSE ' ' END
+ ' INDEX [' + UPPER(INDEXNAME) + ']'
+ SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))
+' ON [' + UPPER(TABLENAME) + '] '
+ SPACE(@MAXTABLELENGTH - LEN(TABLENAME))
+ '(' + UPPER(COLNAMES) + ')'
+ CASE WHEN INDEXFILLFACTOR = 0 THEN '' ELSE ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) END --AS SQL
FROM #TMP
--SELECT * FROM #TMP
DROP TABLE #TMP
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
that is some neat sql there.
seems to have worked after a bit of a fiddle here and there. thanks heaps!!
-
i should have mentioned, and you probably found it...the second sql can produce a ton of results, so i limited it to top 100...you'd pull that out to get all your indexes.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Check out these to script specific objects
http://www.databasejournal.com/features/mssql/article.php/2205291
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply