Procédure StockéeRésolu

Question

Bonjour,

J'ai une base avec des tables (exemple Table1,Table2,...)
Je souhaite faire de façon dynamique une procédure qui me crée une table archive (ex: Table1_old,Table2_old,...) en gardant les clés primaires et les contraintes. Ensuite je me charge d'y insérer la partie des données qui seront placées dans ces tables archives de façon à délester les tables en production. 
Je souhaite actuellement savoir si il existe une procédure stockée qui peut être utilisée pour générer une requete de création (et update) de table dynamiquement ? (exemple Généer un script de type "CREATE TO" ou "ALTER TO" accessible avec le bouton droit de la souris sur la table en elle même) ou bien connaissez vous un autre  moyen  ?

Cette requete dynamique va me permettre de laisser tourner cette procédure de façon automatique le soir, tout en sachant que si je modifie les colonnes d'une table dans la base en production, la table archive sera automatiquement modifiée avec cette procédure ou crée si elle n'existe pas.
Pour les étapes de vérification, insertion des données c'est ok. Mais il me reste à générer dans la procédure la requete Create et Alter de façon dynamique.

Meric de votre aide

Th_Ax · Accepted Answer

Désolé pour la mise en forme... je n'ai pas pu éditer le message 
de plus le tout est utilisé en passant en paramètre le nom de la table ...

CREATE PROCEDURE [PS_archives
@Table varchar(max)

AS
BEGIN

    SET NOCOUNT ON;

DECLARE @sql VARCHAR(8000)
DECLARE @ColName VARCHAR(200)
DECLARE @ColType VARCHAR(100)
DECLARE @ColLength VARCHAR(50)
DECLARE @ColNull VARCHAR(50)
DECLARE @ColDefault VARCHAR(100)

DECLARE MyCur CURSOR LOCAL  
FOR
SELECT 
'[' + column_name + ']' ColName,
'[' + data_type + ']' ColType,
CASE data_type 
    WHEN 'varchar' THEN '(' + cast(character_maximum_length AS VARCHAR) +')'
    WHEN 'nvarchar' THEN '(' + cast(character_maximum_length AS VARCHAR) +')'
    WHEN 'char' THEN '(' + cast(character_maximum_length AS VARCHAR) +')'
    WHEN 'decimal' THEN '(' + cast(numeric_precision AS VARCHAR) +',' + cast(numeric_scale as VARCHAR) +')'
END ColLength,
CASE is_nullable 
    WHEN 'yes' THEN 'null' 
    ELSE 'not null' 
END ColNull,'' ColConstraint,
CASE column_default
    WHEN null THEN ''
    ELSE 'DEFAULT ' + column_default
END ColDefault
FROM INFORMATION_SCHEMA.COLUMNS  iss
INNER JOIN sys.tables st on iss.table_name=st.nameLEFT JOIN sys.default_constraints sdc on sdc.parent_object_id st.object_id AND sdc.parent_column_id iss.ordinal_position
WHERE iss.table_name= @Table

SET @sql = ' CREATE TABLE [A_' + @Table + '] ('

OPEN MyCur
FETCH Mycur INTO @ColName, @ColType, @ColLength, @ColNull, @ColDefault

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @sql = @sql + ' ' + isnull(@colname,'') + ' ' + isnull(@coltype,'') + ' ' + isnull(@collength,'') + ' ' + isnull(@colnull,'') + ' ' + isnull(@ColDefault,'') + ','

FETCH Mycur INTO @ColName,@ColType,@ColLength,@ColNull,@ColDefault
END
CLOSE MyCur
DEALLOCATE MyCur

SET @SQL =  substring(@sql,1,charindex(',',@sql,len(@sql)-2)-1) +')'
-- Execute la requete de créationde table
EXEC (@SQL)

nhervagault · Answer

Salut,

Il faut utiliser les vues systemes sur 2005 ou les tables systemes sur 2000
Exemple avec ma source 
http://www.sqlfr.com/codes_auteur/NHERVAGAULT/84425.aspx

Pour le remplissage du peux faire des ordres insert ... into ... select

Pour le rempplissage tu peux se baser sur cette exemple

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist

Th_Ax · Answer

Salut,

Pour compléter mes explications, plusieurs pistes s'offrent à moi. J'avais pensé à 
SELECT * INTO Table1_old FROM Table1 where 1=0

Cette requete me permet de créer exactement la même structure de table, reste à insérer les données derrières. Cela lorsque la table n'existe pas. En revanche, après un test les contraintes ne sont pas présentes sur la table archive. Il faut que je trouve un moyen de les réinjecter.
Par contre je vais essayer de voir avec les requetes que tu donnes de voir les structures des tables et de les modifier le cas échéant. Ce qui risque d'être fastidieux. Parcourir chaque colonne pour ensuite créer la requete d'update permettant de modifier ou d'jouter une colonne qui n'est pas présente dans la table archive.

JE vais essayer ca aujourd'hui ... Je pensais q'une procédure du type sp_altertable existait permettant de générer une requete de modficiation de la structure en auto, ce quui m'aurait grandement facilité la tache !

Je te tiens au courant.

cs_Firy · Answer

Salut Th_Ax j'éspére que tu trouvras la réponse que tu cherche pour faire la mise a jour dans ta ancienne table..
 pour select *  into Table-anc from table-source je te conseil d'intégrer une clé primaire ds ta Table-anc  car si tu la laisse comm ça tu pe pa faire ni l'ajout ni la mise a jour et je te demande si t'as trouvé une bonn solution écrite la ,à moi car moi aussi je l'ai besoin stp et moi aussi si je trouvrais qqch utile je v t'informer ok et merci

Th_Ax · Answer

Salut,

Voila ou j'en suis actuellement. Je viens de finir la procédure de test et je vais attaquer celle de la création/update des tables. Effectivement tu as raison avec select * into... je vais plutôt utiliser sysobjects pour récupérer les nom de tables et colonnes et tout parcourir pour faire les requêtes nécessaires. Cela va prendre un peu de temps et dès que c'est fait je poste la solution.
Merci

cs_Firy · Answer

Ok merci pour ce code c'est très gentil de ta part je vais le essayer et te dire après.
Merci encore

Procédure Stockée

6 réponses

Votre réponse

Discussions similaires