Baú de Dev

[Script] Exportar todas as chaves primárias (primary keys) de uma base no SQL Server

Estou disponibilizando um script para gerar o DDL de criação de todas as PKs (chaves primárias – primary keys) de uma determinada base de dados no SQL Server.

Recentemente me vi numa situação em que uma base de dados do SQL Server foi migrada apenas com seus dados, sem nenhum tipo de constraint. Na ocasião, foi realizada uma migração usando o utilitário de Import & Export que acompanha a instalação do SQL Server e somente a estrutura básica (colunas) e os dados foram transportados para a base nova.

Consequêntemente foi necessário fazer o levantamento de todas as chaves primárias da base antiga, gerando seus respectivos DDLs e então executá-los na base nova.

O script se encarrega de verificar a ordem dos campos criados originalmente e também se preocupa com as chaves compostas (que possuem mais de uma coluna):

-- Cursor principal 
DECLARE cPK CURSOR FOR
SELECT DISTINCT C.TABLE_NAME, C.CONSTRAINT_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
 
-- Abre o cursor e inicia o loop para cada PK
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName 
WHILE (@@FETCH_STATUS = 0)
BEGIN
	DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
	SET @PKSQL = 'ALTER TABLE DBO.' + @PkTable + ' ADD CONSTRAINT ' + @PkName +
	' PRIMARY KEY ('
 
	-- Identifica todas as colunas que compoem a PK
	DECLARE cPKCol CURSOR FOR
	SELECT 	COLUMN_NAME
	FROM 	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	WHERE 	TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName
	ORDER 	BY ORDINAL_POSITION
 
	OPEN cPKCol
 
	DECLARE @PkCol SYSNAME
	DECLARE @PkFirst BIT SET @PkFirst = 1
 
	-- Abre o cursor e inicia o loop adicionando cada coluna ao comando de CREATE
	FETCH NEXT FROM cPKCol INTO @PkCol
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		IF (@PkFirst = 1)
			SET @PkFirst = 0
		ELSE
			SET @PKSQL = @PKSQL + ', '
 
		SET @PKSQL = @PKSQL + @PkCol
 
		FETCH NEXT FROM cPKCol INTO @PkCol
	END
	CLOSE cPKCol
	DEALLOCATE cPKCol
 
	SET @PKSQL = @PKSQL + ')'
 
	-- Exibe o conteudo de CREATE da PK
	PRINT @PKSQL
 
	FETCH NEXT FROM cPK INTO @PkTable, @PkName
END
CLOSE cPK
DEALLOCATE cPK

Feito isso, basta executá-lo na base de destino e a base está completa!

Em breve postarei também o script que faz um levantamento semelhante, porém extraindo as chaves estrangeiras (foreign keys) da base.

Se este script foi útil pra você, não deixe de comentar mais abaixo e compartilhar sua experiência!

Até mais,

Tagged , , , ,

About Marcelo Santino

Trabalha como analista e administrador de dados em PostgreSQL, Oracle e SQL Server.

View all posts by Marcelo Santino →

Posts Relacionados

8 comentários em “[Script] Exportar todas as chaves primárias (primary keys) de uma base no SQL Server

  1. Weslesson

    Muito Bom .o script.!!! Adptei o mesmo para fazer o drop de todas as pk também, precisei fazer isso para fazer alteração de tipo de dados de todas as colunas pk’s de int para bigint.

    Reply
  2. Fernando Soares Rodrigues

    Tentei execuá-lo na minha base e retornou o erro:

    “Mensagem 16924, Nível 16, Estado 1, Linha 16 Busca do cursor: o número de variáveis declaradas na lista INTO deve corresponder ao número de colunas selecionadas.”

    Este script vai resolver meu problema, estou migrando uma base SQL 2000 (MSDE) para o SQL 2008 R2 Express, a migração dos dados deu certo, mais preciso criar as chaves primárias e estrangeiras, o script das chaves estrangeiras deu certo também.

    Outra questão, existe alguma foram de comprar a estrutura de duas base de dados? gerando um script para fazer a atualização da base antiga deixando-a igual a base nova?

    Muito abrigado pela ajuda!!

    Reply
  3. UESTER

    Parabéns, usei e funcionou e boa, só lembrar de retirar o campo F.NAME da consulta principal, porque senão vai dar um erro falando que tem mais campos na consulta do nas variáveis do cursor.

    Reply

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *