Re: sysindexes

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 05/06/04 

Date: Thu, 6 May 2004 14:35:48 +0100

See if you can get on from this script. I wrote it to change the column
collation, but you should be able to use it for your column change as well.
It is still a work in progress though, so I can't guarantee it will work
completely. 

-- 
Jacco Schalkwijk
SQL Server MVP
-- Initialisation
DECLARE @sql VARCHAR(4000)
DECLARE @debug    BIT
DECLARE @sql_1    VARCHAR(4000)
DECLARE @sql_2    VARCHAR(4000)
DECLARE @sql_3    VARCHAR(4000)
DECLARE @sql_4    VARCHAR(4000)
DECLARE @index_name   SYSNAME
DECLARE @constraint_name  SYSNAME
DECLARE @table_name   SYSNAME
DECLARE @cluster_type  VARCHAR(12)
DECLARE @keyno    TINYINT
DECLARE @collation  SYSNAME
-- Create temporary tables to hold all the information about the object
-- we are going to drop and recreate
SET NOCOUNT OFF
SET @collation = CAST(SERVERPROPERTY('Collation') AS SYSNAME)
Print 'Start'
SET @debug = 1
BEGIN TRAN
CREATE TABLE #COLLATE_COLUMNS(
 TABLE_NAME   VARCHAR(128) NOT NULL,
 COLUMN_NAME   VARCHAR(128) NOT NULL,
 IS_NULLABLE   VARCHAR(3) NOT NULL,
 DATA_TYPE   VARCHAR(128) NOT NULL,
 CHARACTER_MAXIMUM_LENGTH INT,
 PRIMARY KEY(TABLE_NAME, COLUMN_NAME))
CREATE TABLE #CHECK_CONSTRAINTS(
 CONSTRAINT_NAME VARCHAR(128) NOT NULL,
 TABLE_NAME VARCHAR(128) NOT NULL,
 CHECK_CLAUSE VARCHAR(4000) NOT NULL
 PRIMARY KEY(CONSTRAINT_NAME))
CREATE TABLE #UNIQUE_CONSTRAINTS(
 CONSTRAINT_NAME VARCHAR(128) NOT NULL,
 TABLE_NAME VARCHAR(128) NOT NULL,
 CONSTRAINT_TYPE VARCHAR(11) NOT NULL,
 CLUSTER_TYPE VARCHAR(12) NOT NULL,
 PRIMARY KEY (CONSTRAINT_NAME, TABLE_NAME))
CREATE TABLE #UNIQUE_CONSTRAINTS_COLUMNS(
 CONSTRAINT_NAME VARCHAR(128) NOT NULL,
 COLUMN_NAME VARCHAR(128) NOT NULL,
 KEYNO  TINYINT NOT NULL,
 PRIMARY KEY(CONSTRAINT_NAME, COLUMN_NAME))
CREATE TABLE #FOREIGN_KEYS(
 CONSTRAINT_NAME VARCHAR(128) NOT NULL,
 FK_TABLE_NAME VARCHAR(128) NOT NULL,
 PK_TABLE_NAME VARCHAR(128) NOT NULL,
 UPDATE_RULE VARCHAR(9) NOT NULL,
 DELETE_RULE VARCHAR(9) NOT NULL,
 FK_COLUMN_1 VARCHAR(128) NULL,
 FK_COLUMN_2 VARCHAR(128) NULL,
 FK_COLUMN_3 VARCHAR(128) NULL,
 FK_COLUMN_4 VARCHAR(128) NULL,
 FK_COLUMN_5 VARCHAR(128) NULL,
 FK_COLUMN_6 VARCHAR(128) NULL,
 FK_COLUMN_7 VARCHAR(128) NULL,
 FK_COLUMN_8 VARCHAR(128) NULL,
 FK_COLUMN_9 VARCHAR(128) NULL,
 FK_COLUMN_10 VARCHAR(128) NULL,
 FK_COLUMN_11 VARCHAR(128) NULL,
 FK_COLUMN_12 VARCHAR(128) NULL,
 FK_COLUMN_13 VARCHAR(128) NULL,
 FK_COLUMN_14 VARCHAR(128) NULL,
 FK_COLUMN_15 VARCHAR(128) NULL,
 FK_COLUMN_16 VARCHAR(128) NULL,
 PK_COLUMN_1 VARCHAR(128) NULL,
 PK_COLUMN_2 VARCHAR(128) NULL,
 PK_COLUMN_3 VARCHAR(128) NULL,
 PK_COLUMN_4 VARCHAR(128) NULL,
 PK_COLUMN_5 VARCHAR(128) NULL,
 PK_COLUMN_6 VARCHAR(128) NULL,
 PK_COLUMN_7 VARCHAR(128) NULL,
 PK_COLUMN_8 VARCHAR(128) NULL,
 PK_COLUMN_9 VARCHAR(128) NULL,
 PK_COLUMN_10 VARCHAR(128) NULL,
 PK_COLUMN_11 VARCHAR(128) NULL,
 PK_COLUMN_12 VARCHAR(128) NULL,
 PK_COLUMN_13 VARCHAR(128) NULL,
 PK_COLUMN_14 VARCHAR(128) NULL,
 PK_COLUMN_15 VARCHAR(128) NULL,
 PK_COLUMN_16 VARCHAR(128) NULL,
 PRIMARY KEY (CONSTRAINT_NAME))
CREATE TABLE #COMPUTED_COLUMNS(
 TABLE_NAME VARCHAR(128) NOT NULL,
 COLUMN_NAME VARCHAR(128) NOT NULL,
 FORMULA  VARCHAR(4000) NOT NULL,
 PRIMARY KEY (TABLE_NAME, COLUMN_NAME))
CREATE TABLE #INDEXES(
 INDEX_NAME VARCHAR(128) NOT NULL,
 TABLE_NAME VARCHAR(128) NOT NULL,
 CLUSTER_TYPE VARCHAR(12) NOT NULL,
 PRIMARY KEY (INDEX_NAME, TABLE_NAME))
CREATE TABLE #INDEX_COLUMNS(
 INDEX_NAME VARCHAR(128) NOT NULL,
 TABLE_NAME VARCHAR(128) NOT NULL,
 COLUMN_NAME VARCHAR(128) NOT NULL,
 KEYNO  TINYINT,
 PRIMARY KEY (INDEX_NAME, TABLE_NAME, COLUMN_NAME))
-- Fill the temporary tables with the relevant information _and_ drop
-- the objects.
Print 'Tables created'
INSERT INTO #COLLATE_COLUMNS(
 TABLE_NAME,
 COLUMN_NAME,
 IS_NULLABLE,
 DATA_TYPE,
 CHARACTER_MAXIMUM_LENGTH
 )
SELECT
 c.table_name,
 c.column_name,
 c.is_nullable,
 c.data_type,
 c.character_maximum_length
 FROM information_schema.columns c
 INNER JOIN information_schema.tables t
 ON c.table_name = t.table_name
 WHERE (c.collation_name <> @collation
 AND t.table_type = 'BASE TABLE')
INSERT INTO #CHECK_CONSTRAINTS (
 CONSTRAINT_NAME,
 TABLE_NAME,
 CHECK_CLAUSE
 )
SELECT
 cc.CONSTRAINT_NAME,
 tc.TABLE_NAME,
 cc.CHECK_CLAUSE
FROM information_schema.check_constraints cc
INNER JOIN information_schema.table_constraints tc
ON cc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE EXISTS(SELECT NULL FROM #COLLATE_COLUMNS col
 WHERE tc.TABLE_NAME = col.TABLE_NAME)
Print '#CHECK_CONSTRAINTS filled'
WHILE 1=1
BEGIN
 SET @sql = (SELECT TOP 1 'ALTER TABLE ' + QUOTENAME(TABLE_NAME)
  + ' DROP CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME)
  FROM #CHECK_CONSTRAINTS
  WHERE EXISTS(SELECT NULL FROM information_schema.table_constraints tc
   WHERE tc.constraint_name = #CHECK_CONSTRAINTS.CONSTRAINT_NAME))
 IF @sql IS NULL BREAK
 IF @debug = 1 PRINT @sql
 EXEC (@sql)
END
INSERT INTO #FOREIGN_KEYS(
 CONSTRAINT_NAME,
 FK_TABLE_NAME,
 PK_TABLE_NAME,
 UPDATE_RULE,
 DELETE_RULE,
 FK_COLUMN_1 ,
 FK_COLUMN_2 ,
 FK_COLUMN_3 ,
 FK_COLUMN_4 ,
 FK_COLUMN_5 ,
 FK_COLUMN_6 ,
 FK_COLUMN_7 ,
 FK_COLUMN_8 ,
 FK_COLUMN_9 ,
 FK_COLUMN_10 ,
 FK_COLUMN_11 ,
 FK_COLUMN_12 ,
 FK_COLUMN_13 ,
 FK_COLUMN_14 ,
 FK_COLUMN_15 ,
 FK_COLUMN_16 ,
 PK_COLUMN_1 ,
 PK_COLUMN_2 ,
 PK_COLUMN_3 ,
 PK_COLUMN_4 ,
 PK_COLUMN_5 ,
 PK_COLUMN_6 ,
 PK_COLUMN_7 ,
 PK_COLUMN_8 ,
 PK_COLUMN_9 ,
 PK_COLUMN_10 ,
 PK_COLUMN_11 ,
 PK_COLUMN_12 ,
 PK_COLUMN_13 ,
 PK_COLUMN_14 ,
 PK_COLUMN_15 ,
 PK_COLUMN_16
 )
SELECT OBJECT_NAME(sr.constid) AS CONSTRAINT_NAME,
 OBJECT_NAME(sr.fkeyid) AS FK_TABLE_NAME,
 OBJECT_NAME(sr.rkeyid) AS PK_TABLE_NAME,
 CASE OBJECTPROPERTY(sr.constid, 'CnstIsDeleteCascade')
  WHEN 0 THEN 'NO ACTION'
  WHEN 1 THEN 'CASCADE'
 END AS DELETE_ACTION,
 CASE OBJECTPROPERTY(sr.constid, 'CnstIsUpdateCascade')
  WHEN 0 THEN 'NO ACTION'
  WHEN 1 THEN 'CASCADE'
 END AS UPDATE_ACTION,
 fkcol_1.name AS FK_COLUMN_NAME_1,
 fkcol_2.name AS FK_COLUMN_NAME_2,
 fkcol_3.name AS FK_COLUMN_NAME_3,
 fkcol_4.name AS FK_COLUMN_NAME_4,
 fkcol_5.name AS FK_COLUMN_NAME_5,
 fkcol_6.name AS FK_COLUMN_NAME_6,
 fkcol_7.name AS FK_COLUMN_NAME_7,
 fkcol_8.name AS FK_COLUMN_NAME_8,
 fkcol_9.name AS FK_COLUMN_NAME_9,
 fkcol_10.name AS FK_COLUMN_NAME_10,
 fkcol_11.name AS FK_COLUMN_NAME_11,
 fkcol_12.name AS FK_COLUMN_NAME_12,
 fkcol_13.name AS FK_COLUMN_NAME_13,
 fkcol_14.name AS FK_COLUMN_NAME_14,
 fkcol_15.name AS FK_COLUMN_NAME_15,
 fkcol_16.name AS FK_COLUMN_NAME_16,
 pkcol_1.name AS PK_COLUMN_NAME_1,
 pkcol_2.name AS PK_COLUMN_NAME_2,
 pkcol_3.name AS PK_COLUMN_NAME_3,
 pkcol_4.name AS PK_COLUMN_NAME_4,
 pkcol_5.name AS PK_COLUMN_NAME_5,
 pkcol_6.name AS PK_COLUMN_NAME_6,
 pkcol_7.name AS PK_COLUMN_NAME_7,
 pkcol_8.name AS PK_COLUMN_NAME_8,
 pkcol_9.name AS PK_COLUMN_NAME_9,
 pkcol_10.name AS PK_COLUMN_NAME_10,
 pkcol_11.name AS PK_COLUMN_NAME_11,
 pkcol_12.name AS PK_COLUMN_NAME_12,
 pkcol_13.name AS PK_COLUMN_NAME_13,
 pkcol_14.name AS PK_COLUMN_NAME_14,
 pkcol_15.name AS PK_COLUMN_NAME_15,
 pkcol_16.name AS PK_COLUMN_NAME_16
FROM sysreferences sr
LEFT OUTER JOIN syscolumns fkcol_1 ON sr.fkeyid = fkcol_1.id AND sr.fkey1 =
fkcol_1.colid
LEFT OUTER JOIN syscolumns fkcol_2 ON sr.fkeyid = fkcol_2.id AND sr.fkey2 =
fkcol_2.colid
LEFT OUTER JOIN syscolumns fkcol_3 ON sr.fkeyid = fkcol_3.id AND sr.fkey3 =
fkcol_3.colid
LEFT OUTER JOIN syscolumns fkcol_4 ON sr.fkeyid = fkcol_4.id AND sr.fkey4 =
fkcol_4.colid
LEFT OUTER JOIN syscolumns fkcol_5 ON sr.fkeyid = fkcol_5.id AND sr.fkey5 =
fkcol_5.colid
LEFT OUTER JOIN syscolumns fkcol_6 ON sr.fkeyid = fkcol_6.id AND sr.fkey6 =
fkcol_6.colid
LEFT OUTER JOIN syscolumns fkcol_7 ON sr.fkeyid = fkcol_7.id AND sr.fkey7 =
fkcol_7.colid
LEFT OUTER JOIN syscolumns fkcol_8 ON sr.fkeyid = fkcol_8.id AND sr.fkey8 =
fkcol_8.colid
LEFT OUTER JOIN syscolumns fkcol_9 ON sr.fkeyid = fkcol_9.id AND sr.fkey9 =
fkcol_9.colid
LEFT OUTER JOIN syscolumns fkcol_10 ON sr.fkeyid = fkcol_10.id AND sr.fkey10
= fkcol_10.colid
LEFT OUTER JOIN syscolumns fkcol_11 ON sr.fkeyid = fkcol_11.id AND sr.fkey11
= fkcol_11.colid
LEFT OUTER JOIN syscolumns fkcol_12 ON sr.fkeyid = fkcol_12.id AND sr.fkey12
= fkcol_12.colid
LEFT OUTER JOIN syscolumns fkcol_13 ON sr.fkeyid = fkcol_13.id AND sr.fkey13
= fkcol_13.colid
LEFT OUTER JOIN syscolumns fkcol_14 ON sr.fkeyid = fkcol_14.id AND sr.fkey14
= fkcol_14.colid
LEFT OUTER JOIN syscolumns fkcol_15 ON sr.fkeyid = fkcol_15.id AND sr.fkey15
= fkcol_15.colid
LEFT OUTER JOIN syscolumns fkcol_16 ON sr.fkeyid = fkcol_16.id AND sr.fkey16
= fkcol_16.colid
LEFT OUTER JOIN syscolumns pkcol_1 ON sr.rkeyid = pkcol_1.id AND sr.rkey1 =
pkcol_1.colid
LEFT OUTER JOIN syscolumns pkcol_2 ON sr.rkeyid = pkcol_2.id AND sr.rkey2 =
pkcol_2.colid
LEFT OUTER JOIN syscolumns pkcol_3 ON sr.rkeyid = pkcol_3.id AND sr.rkey3 =
pkcol_3.colid
LEFT OUTER JOIN syscolumns pkcol_4 ON sr.rkeyid = pkcol_4.id AND sr.rkey4 =
pkcol_4.colid
LEFT OUTER JOIN syscolumns pkcol_5 ON sr.rkeyid = pkcol_5.id AND sr.rkey5 =
pkcol_5.colid
LEFT OUTER JOIN syscolumns pkcol_6 ON sr.rkeyid = pkcol_6.id AND sr.rkey6 =
pkcol_6.colid
LEFT OUTER JOIN syscolumns pkcol_7 ON sr.rkeyid = pkcol_7.id AND sr.rkey7 =
pkcol_7.colid
LEFT OUTER JOIN syscolumns pkcol_8 ON sr.rkeyid = pkcol_8.id AND sr.rkey8 =
pkcol_8.colid
LEFT OUTER JOIN syscolumns pkcol_9 ON sr.rkeyid = pkcol_9.id AND sr.rkey9 =
pkcol_9.colid
LEFT OUTER JOIN syscolumns pkcol_10 ON sr.rkeyid = pkcol_10.id AND sr.rkey10
= pkcol_10.colid
LEFT OUTER JOIN syscolumns pkcol_11 ON sr.rkeyid = pkcol_11.id AND sr.rkey11
= pkcol_11.colid
LEFT OUTER JOIN syscolumns pkcol_12 ON sr.rkeyid = pkcol_12.id AND sr.rkey12
= pkcol_12.colid
LEFT OUTER JOIN syscolumns pkcol_13 ON sr.rkeyid = pkcol_13.id AND sr.rkey13
= pkcol_13.colid
LEFT OUTER JOIN syscolumns pkcol_14 ON sr.rkeyid = pkcol_14.id AND sr.rkey14
= pkcol_14.colid
LEFT OUTER JOIN syscolumns pkcol_15 ON sr.rkeyid = pkcol_15.id AND sr.rkey15
= pkcol_15.colid
LEFT OUTER JOIN syscolumns pkcol_16 ON sr.rkeyid = pkcol_16.id AND sr.rkey16
= pkcol_16.colid
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON OBJECT_NAME(sr.constid) = rc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu_fk
ON rc.CONSTRAINT_NAME = ccu_fk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu_pk
ON rc.UNIQUE_CONSTRAINT_NAME = ccu_pk.CONSTRAINT_NAME
WHERE EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
ccu1
 INNER JOIN INFORMATION_SCHEMA.COLUMNS c
  ON ccu1.TABLE_NAME = c.TABLE_NAME
  AND ccu1.COLUMN_NAME = c.COLUMN_NAME
 WHERE c.COLLATION_NAME <> @collation
 AND (ccu1.CONSTRAINT_NAME = rc.CONSTRAINT_NAME))
-- Drop the foreign keys
--DECLARE @sql_1    VARCHAR(4000)
WHILE 1=1
BEGIN
 SET @sql = (SELECT TOP 1 'ALTER TABLE ' + QUOTENAME(FK_TABLE_NAME)
  + ' DROP CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME)
  FROM #FOREIGN_KEYS
  WHERE EXISTS(SELECT NULL FROM information_schema.table_constraints tc
   WHERE tc.constraint_name = #FOREIGN_KEYS.CONSTRAINT_NAME))
 IF @sql IS NULL BREAK
 EXEC (@sql)
 IF @debug  = 1 PRINT @sql
END
INSERT INTO #COMPUTED_COLUMNS(
 TABLE_NAME,
 COLUMN_NAME,
 FORMULA)
SELECT c.column_name, c.table_name, sc.text
FROM information_schema.columns c
INNER JOIN information_schema.tables t
 ON c.table_name  = t.table_name
INNER JOIN syscolumns col
 ON c.table_name = object_name(col.id)
 AND c.column_name = col.name
INNER JOIN syscomments sc
ON col.id = sc.id AND col.colid = sc.colid
WHERE t.table_type = 'BASE TABLE'
INSERT INTO #UNIQUE_CONSTRAINTS(
 CONSTRAINT_NAME,
 TABLE_NAME,
 CONSTRAINT_TYPE,
 CLUSTER_TYPE
 )
SELECT  tc.constraint_name,
 tc.table_name,
 tc.constraint_type,
 CASE INDEXPROPERTY(OBJECT_ID(tc.table_name), tc.constraint_name,
'IsClustered')
  WHEN  1 THEN 'CLUSTERED'
  ELSE 'NONCLUSTERED'
 END AS ClusterType
FROM information_schema.table_constraints tc
WHERE tc.CONSTRAINT_TYPE IN ('UNIQUE', 'PRIMARY KEY')
 AND EXISTS(SELECT NULL FROM #COLLATE_COLUMNS cc
  INNER JOIN information_schema.constraint_column_usage ccu
  ON ccu.TABLE_NAME = cc.TABLE_NAME
  AND ccu.COLUMN_NAME = cc.COLUMN_NAME
  INNER JOIN information_schema.referential_constraints rc
  ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
   OR ccu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
  WHERE ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME)
INSERT INTO #UNIQUE_CONSTRAINTS_COLUMNS(
 CONSTRAINT_NAME,
 COLUMN_NAME,
 KEYNO)
SELECT  si.name,
 col.name,
 sik.keyno
 FROM sysindexes si
 INNER JOIN sysindexkeys sik
 ON si.id = sik.id AND si.indid = sik.indid
 INNER JOIN syscolumns col
 ON sik.id = col.id AND sik.colid = col.colid
 INNER JOIN #UNIQUE_CONSTRAINTS uc
 ON si.name = uc.constraint_name
WHILE 1=1
BEGIN
 SET @sql = (SELECT TOP 1 'ALTER TABLE ' + QUOTENAME(TABLE_NAME)
  + ' DROP CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME)
  FROM #UNIQUE_CONSTRAINTS
  WHERE EXISTS(SELECT NULL FROM information_schema.table_constraints tc
   WHERE tc.CONSTRAINT_NAME = #UNIQUE_CONSTRAINTS.CONSTRAINT_NAME))
 IF @sql IS NULL BREAK
 IF @debug = 1 PRINT @sql
 EXEC (@sql)
END
INSERT INTO #INDEXES(
 TABLE_NAME,
 INDEX_NAME,
 CLUSTER_TYPE
 )
SELECT
 OBJECT_NAME(id) AS Table_Name,
 name AS Index_Name,
 CASE INDEXPROPERTY(si.id, si.name, 'IsClustered')
  WHEN  1 THEN 'CLUSTERED'
  ELSE 'NONCLUSTERED'
 END AS ClusterType
FROM sysindexes  si
WHERE INDEXPROPERTY(si.id, si.name, 'IsHypothetical') = 0
 AND INDEXPROPERTY(si.id, si.name, 'IsStatistics') = 0
 AND INDEXPROPERTY(si.id, si.name, 'IsFulltextKey') = 0
 AND OBJECTPROPERTY(si.id, 'IsMSShipped') = 0
 AND si.indid < 255
 AND EXISTS(SELECT NULL FROM sysindexkeys sik
  INNER JOIN syscolumns sc
  ON sik.id = sc.id AND sik.colid = sc.colid
  WHERE  sc.COLLATION <> @collation
  AND sik.id = si.id AND sik.indid = si.indid)
INSERT INTO #INDEX_COLUMNS(
 TABLE_NAME,
 INDEX_NAME,
 COLUMN_NAME,
 KEYNO)
SELECT
 OBJECT_NAME(sik.id) AS Table_Name,
 si.name AS Index_Name,
 col.name AS column_name,
 keyno
FROM sysindexkeys sik
INNER JOIN sysindexes si
 ON sik.id = si.id
 AND sik.indid = si.indid
INNER JOIN syscolumns col
 ON sik.id = col.id
 AND sik.colid = col.colid
INNER JOIN #INDEXES i
 ON i.INDEX_NAME = si.name
 AND i.TABLE_NAME = OBJECT_NAME(si.id)
WHILE 1=1
BEGIN
 SET @sql = (SELECT TOP 1 'DROP INDEX ' + QUOTENAME(TABLE_NAME) + '.' +
QUOTENAME(INDEX_NAME)
  FROM #INDEXES
  WHERE EXISTS(SELECT NULL FROM sysindexes
   WHERE OBJECT_NAME(id) = TABLE_NAME
   AND name = INDEX_NAME))
 IF @sql IS NULL BREAK
 IF @debug = 1 PRINT @sql
 EXEC (@sql)
END
IF @debug = 1
BEGIN
 SELECT * FROM #CHECK_CONSTRAINTS
 SELECT * FROM #FOREIGN_KEYS
 SELECT * FROM #COMPUTED_COLUMNS
 SELECT * FROM #INDEXES
 SELECT * FROM #INDEX_COLUMNS
END
-- Alter the database collation
-- ALTER DATABASE RMIS COLLATE Latin1_General_CI_AS
WHILE 1=1
BEGIN
 SET @sql = (SELECT TOP 1 'ALTER TABLE ' + QUOTENAME(c.TABLE_NAME)
  + ' ALTER COLUMN ' + QUOTENAME(c.COLUMN_NAME) + ' '
  + c.DATA_TYPE + '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
  + ') COLLATE ' + @collation
  + ' ' + CASE c.IS_NULLABLE WHEN 'YES' THEN 'NULL' WHEN 'No' THEN 'NOT
NULL' END
  FROM information_schema.columns c
   INNER JOIN information_schema.tables t
   ON c.table_name = t.table_name
   WHERE c.collation_name <> @collation
   AND t.table_type = 'BASE TABLE')
 IF @sql IS NULL BREAK
 IF @debug = 1 PRINT @sql
 EXEC (@sql)
END
-- Recreate the foreign keys
WHILE 1=1
BEGIN
 SET @constraint_name = (SELECT TOP 1 CONSTRAINT_NAME
  FROM #FOREIGN_KEYS
  WHERE NOT EXISTS(SELECT NULL FROM information_schema.table_constraints tc
   WHERE tc.constraint_name = #FOREIGN_KEYS.CONSTRAINT_NAME))
 IF @constraint_name IS NULL BREAK
 SELECT
  @sql_1 = 'ALTER TABLE ' + QUOTENAME(FK_TABLE_NAME)
   + ' ADD CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME) + ' FOREIGN KEY ('
   + COALESCE(QUOTENAME(FK_COLUMN_1), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_2), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_3), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_4), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_5), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_6), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_7), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_8), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_9), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_10), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_11), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_12), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_13), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_14), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_15), '')
   + COALESCE(', ' + QUOTENAME(FK_COLUMN_16), '')
  +') REFERENCES ' + QUOTENAME(PK_TABLE_NAME) + ' ('
   + COALESCE(QUOTENAME(PK_COLUMN_1), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_2), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_3), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_4), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_5), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_6), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_7), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_8), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_9), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_10), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_11), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_12), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_13), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_14), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_15), '')
   + COALESCE(', ' + QUOTENAME(PK_COLUMN_16), '')
  + ') ON DELETE '+ DELETE_RULE + ' ON UPDATE ' + UPDATE_RULE
  FROM #FOREIGN_KEYS
  WHERE CONSTRAINT_NAME = @constraint_name
 EXEC(@sql_1)
 IF @debug = 1 PRINT @sql_1
END
WHILE 1=1
 BEGIN
 SET ROWCOUNT 1
  SELECT TOP 1 @index_name = INDEX_NAME,
  @table_name = TABLE_NAME,
  @cluster_type = CLUSTER_TYPE
  FROM #INDEXES
  WHERE NOT EXISTS(SELECT NULL FROM sysindexes
   WHERE OBJECT_NAME(id) = TABLE_NAME
   AND name = INDEX_NAME)
  ORDER BY CLUSTER_TYPE -- Build clustered indexes first
 SET ROWCOUNT 0
 IF @index_name IS NULL BREAK
 IF NOT EXISTS(SELECT NULL FROM #INDEXES) BREAK
 SET @sql_1 = 'CREATE ' + @cluster_type + ' INDEX ' + QUOTENAME(@index_name)
  + ' ON ' + QUOTENAME(@table_name) + '('
 SET @keyno = 0
 WHILE 1=1
 BEGIN
  SET @keyno = (SELECT MIN(KEYNO) FROM #INDEX_COLUMNS
   WHERE INDEX_NAME = @index_name
   AND TABLE_NAME = @table_name
   AND KEYNO > @keyno)
  IF @keyno IS NULL BREAK
  SET @sql_1 = @sql_1 + (SELECT QUOTENAME(COLUMN_NAME) + ', '
   FROM #INDEX_COLUMNS
   WHERE INDEX_NAME = @index_name
   AND TABLE_NAME = @table_name
   AND KEYNO = @keyno)
 END
 SET @sql_1 = LEFT(RTRIM(@sql_1), LEN(RTRIM(@sql_1)) -1) + ')'
 IF @debug = 1 PRINT @sql_1
 IF NOT EXISTS(SELECT NULL FROM information_schema.table_constraints
   WHERE CONSTRAINT_NAME = @index_name)
  EXEC (@sql_1)
 DELETE FROM #INDEXES WHERE INDEX_NAME = @index_name AND TABLE_NAME =
@table_name
 DELETE FROM #INDEX_COLUMNS WHERE INDEX_NAME = @index_name AND TABLE_NAME =
@table_name
END
WHILE 1=1
BEGIN
 SET @sql = (SELECT TOP 1 'ALTER TABLE ' + QUOTENAME(TABLE_NAME)
  + ' ADD CONSTRAINT ' + QUOTENAME(CONSTRAINT_NAME)
  + ' CHECK ' + CHECK_CLAUSE
  FROM #CHECK_CONSTRAINTS
  WHERE NOT EXISTS(SELECT NULL FROM information_schema.table_constraints tc
   WHERE tc.constraint_name = #CHECK_CONSTRAINTS.CONSTRAINT_NAME))
 IF @sql IS NULL BREAK
 IF @debug = 1 PRINT @sql
 EXEC (@sql)
END
DROP TABLE #CHECK_CONSTRAINTS, #FOREIGN_KEYS, #COMPUTED_COLUMNS, #INDEXES,
#INDEX_COLUMNS
IF @@TRANCOUNT > 0 AND @debug = 1
 ROLLBACK TRAN
IF @@TRANCOUNT > 0 AND @debug = 0
 COMMIT TRAN
<anonymous@discussions.microsoft.com> wrote in message
news:949e01c4336d$a59936b0$a001280a@phx.gbl...
> Hi,
>
> We are in the process of converting our application DB
> char and varchar columns to nchar/nvarchar
> recpectively.Our application suuports dynamic tables i.e
> each customer will have set of standard tables and dynamic
> tables which are created based on his application
> customization..
>
> I am following the below methodology for the same.
>
> 1.Generate script for tables ,constraints and indexes.
>
> 2.drop constraints.
>
> 3.drop all indexes
>
> 4.alter tables to have nchar and nvarchar
>
> 4.using script created in the step..recreate all
> constraints and indexes..
>
> Pls evaluate my methodology and help me in getting scripts
> for the steps 2,3,4 .....
>
>
> Sridhar
>
>
> >-----Original Message-----
> >What do you actually want to achieve? You ahve to take
> into account that
> >there are entries in sysindexes as well for indexes that
> a created when you
> >create a Primary key or Unique constraint, and you can't
> drop them with DROP
> >INDEX, you have to use DROP CONSTRAINT for them. Do you
> wan tto drop them as
> >well? And what do you mean with "dynamic" tables?
> >
> >-- 
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> ><anonymous@discussions.microsoft.com> wrote in message
> >news:93bb01c4335e$7db89e10$a001280a@phx.gbl...
> >>
> >> select 'Drop index ' + a.name + '.'+ b.name + char(13) +
> >> char(10) + 'go'
> >> from   sysobjects a , sysindexes b
> >> where  a.id=b.id
> >> and    a.type='U'
> >> order  by a.name
> >>
> >> Then how do i drop all indexes on my application
> tables ..
> >> It contains dynamic tables too...
> >>
> >> >-----Original Message-----
> >> >Not all entries in sysindexes are indexes that you can
> >> drop. Some are
> >> >statistics that can't be dropped with DROP INDEX, and
> >> indexes with indid =0
> >> >(table with no clustered index) or 255 (text, ntext,
> >> image column) can't be
> >> >dropped either. It would be helpful if you post your
> >> script.
> >> >
> >> >-- 
> >> >Jacco Schalkwijk
> >> >SQL Server MVP
> >> >
> >> >
> >> ><anonymous@discussions.microsoft.com> wrote in message
> >> >news:920201c43343$d3f54af0$a101280a@phx.gbl...
> >> >> will there be references for dropped indexes in
> >> >> sysindexes..?
> >> >>
> >> >> I created a script to drop all indexes..when i ran
> some
> >> of
> >> >> them got dropped..and some threw error..when i re-ran
> >> the
> >> >> script it says that index is not system
> catalog..however
> >> >> when i query sysindexes they are still there..
> >> >>
> >> >> any help on this is welcome..
> >> >>
> >> >> Sridhar
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Relevant Pages