Gerar script com primary keys e foreign keys no SQL SERVER 2005

No SQL Server 2000 era relativamente fácil gerar um script apenas com as integridades referenciais. Bastava acessar a base com o Enterprise Manager, botão direito na base, opção Generate Scripts e checar a opção “SCRIPT PRIMARY KEYS, FOREIGN KEYS, DEFAULTS AND CHECK CONSTRAINTS”. O gerador de scripts separava os scripts com clareza entre criação de tabelas e as respectivas constraints.

Porém, com o Management Studio os scripts de tabelas primárias e estrangeiras ficam chumbados com o comando CREATE TABLE, o que dificulta a sua execução quando as tabelas já existem e queremos apenas aplicar as constraints.

CREATE TABLE [dbo].[tb_acao_responsavel](

      [id_acao] [int] IDENTITY(1,1) NOT NULL,

      [ds_acao] [varchar](50) NOT NULL,

      [ic_ativo] [char](1) NOT NULL CONSTRAINT [df_tb_acao_01]  DEFAULT ('S'),

 CONSTRAINT [pk_tb_acao] PRIMARY KEY CLUSTERED

(

      [id_acao] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

GO

Localizei na internet, um script interessante que gera todas as chaves primárias e estrangeiras para a base onde está sendo executada. Para utilizar corretamente o script, mude o output do Management Studio para Text.

DECLARE cpk CURSOR FOR

  SELECT DISTINCT c.table_name,

                  c.constraint_name,

                  f.name

  FROM   sys.indexes i

         INNER JOIN sys.filegroups f

           ON i.data_space_id = f.data_space_id

         INNER JOIN sys.all_objects o

           ON i.[OBJECT_ID] = o.[OBJECT_ID]

         INNER JOIN information_schema.table_constraints c

           ON o.name = c.table_name

  WHERE  c.constraint_type = 'PRIMARY KEY'

  ORDER  BY c.table_name

DECLARE @PkTable SYSNAME

DECLARE @PkName SYSNAME

DECLARE @FileName SYSNAME

 

-- Loop through all the primary keys

OPEN cpk

 

FETCH NEXT FROM cpk INTO @PkTable, @PkName, @FileName

 

WHILE ( @@FETCH_STATUS = 0 )

  BEGIN

      DECLARE @PKSQL NVARCHAR(4000)

 

      SET @PKSQL = ''

      SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT ' + @PkName +

                   ' PRIMARY KEY CLUSTERED ('

 

      -- Get all columns for the current primary key

      DECLARE cpkcolumn CURSOR FOR

        SELECT column_name

        FROM   information_schema.key_column_usage

        WHERE  table_name = @PkTable

               AND constraint_name = @PkName

        ORDER  BY ordinal_position

 

      OPEN cpkcolumn

 

      DECLARE @PkColumn SYSNAME

      DECLARE @PkFirstColumn BIT

 

      SET @PkFirstColumn = 1

 

      -- Loop through all columns and append the sql statement

      FETCH NEXT FROM cpkcolumn INTO @PkColumn

 

      WHILE ( @@FETCH_STATUS = 0 )

        BEGIN

            IF ( @PkFirstColumn = 1 )

              SET @PkFirstColumn = 0

            ELSE

              SET @PKSQL = @PKSQL + ', '

 

            SET @PKSQL = @PKSQL + @PkColumn

 

            FETCH NEXT FROM cpkcolumn INTO @PkColumn

        END

 

      CLOSE cpkcolumn

 

      DEALLOCATE cpkcolumn

 

      SET @PKSQL = @PKSQL + ')' + ' ON ' + @FileName

 

      -- Print the primary key statement

      PRINT @PKSQL

 

      FETCH NEXT FROM cpk INTO @PkTable, @PkName, @FileName

  END

 

CLOSE cpk

 

DEALLOCATE cpk

 

---*********Generate create script for all Foreign Keys

SELECT 'ALTER TABLE ' + Object_name(f.parent_object_id) + ' ADD CONSTRAINT' +

       f.name +

' FOREIGN KEY' + '(' + Col_name(fc.parent_object_id, fc.parent_column_id) + ')'

+ 'REFRENCES ' + Object_name (f.referenced_object_id) + '('

       +

       Col_name(fc.referenced_object_id, fc.referenced_column_id) + ')'

FROM   sys.foreign_keys AS f

       INNER JOIN sys.foreign_key_columns AS fc

         ON f.object_id = fc.constraint_object_id 

Script localizado no blog www.connectsql.blogspot.com . Postagem original: http://connectsql.blogspot.com/2009/07/script-to-create-all-primary-and.html .

Até a próxima!