AG14Am1v

1IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
2WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'Persons' 
3AND TABLE_SCHEMA ='dbo')
4BEGIN
5   ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
6END
7	
8SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
9	
10SELECT * 
11FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
12WHERE TABLE_NAME = 'Persons' 
13and COLUMN_NAME = 'P_Id' 
14AND TABLE_SCHEMA ='dbo'
15	
16ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
17	
18IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
19WHERE TABLE_NAME = 'Persons' and COLUMN_NAME = 'P_Id' 
20AND TABLE_SCHEMA ='dbo')
21BEGIN
22   ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
23END
24	
25Declare @Parametros as table(id int identity(1,1),tabela varchar(100),campo varchar(100))
26    Declare @tabela varchar(100)
27    Declare @campo varchar(100)
28    Declare @id int
29    Declare @ok int
30
31/* seus campos conforme o create */
32
33    insert into @Parametros
34    Values('agencia','codagencia'),
35          ('conta','codconta'),
36          ('tipomovimento','codtipomov'),
37          ('emprestimo','codemprestimo'),
38          ('clienteconta','codclienteconta')
39
40    SELECT @ok = count(*) from @Parametros
41
42    Declare @SQL VARCHAR(MAX) = '
43    create table emprestimo(
44        codemprestimo smallint,
45        dataemprestimo varchar(10),
46        valoremprestimo float,
47        taxajuro float,
48        numeroemprestimo smallint,
49        observacoes varchar(100),
50        codagencia smallint,
51            foreign key(codagencia) references agencia(codagencia)
52            on update cascade
53            on delete cascade,
54            codconta smallint,
55            foreign key(codconta) references conta(codconta)
56            on update cascade
57            on delete cascade,
58            primary key(codemprestimo));
59    GO
60    create table movimentoconta(
61    codmovconta smallint,
62    datamov varchar(10),
63    valormov float,
64    codtipomov smallint,
65        foreign key(codtipomov) references tipomovimento(codtipomov)
66        on update cascade
67        on delete cascade,
68        codemprestimo smallint,
69        foreign key (codemprestimo) references emprestimo(codemprestimo)
70        on update cascade
71        on delete cascade,
72        codclienteconta smallint,
73        foreign key(codclienteconta) references clienteconta(codclienteconta)
74        on update cascade
75        on delete cascade,
76        primary key(codmovconta));'
77
78
79
80    while exists (select top 1 1 from @Parametros)
81    begin
82
83    select top 1
84            @id = id 
85          , @tabela = tabela
86          , @campo = campo           
87      from @Parametros
88
89    if exists (select top 1 1 from sys.tables where name = @tabela )
90    BEGIN
91    if exists (SELECT TOP 1 1 FROM sys.sysobjects AS T (NOLOCK) 
92                    INNER JOIN sys.all_columns AS C (NOLOCK) ON T.id = C.object_id AND T.XTYPE = 'U' 
93                    WHERE T.NAME = @tabela AND C.NAME = @campo )
94    BEGIN
95    if exists (SELECT TOP 1 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
96                WHERE TABLE_NAME = @tabela 
97                  AND COLUMN_NAME = @campo
98                  AND TABLE_SCHEMA ='dbo' )
99    BEGIN
100    set @ok = @ok - 1
101    if @ok <= 0
102    begin
103    SELECT 'Create OK, rode o script ==> ', @SQL as Script
104    end
105    END ELSE BEGIN select ('CAMPO INFORMADO ' + @campo + ' NÃO É PK') break; END
106    END ELSE BEGIN select ('CAMPO '+ @campo +' NÃO EXISTE NA TABELA') break; END
107    END ELSE BEGIN select ('TABELA '+ @tabela +' NÃO EXISTE')         break; END
108
109     delete from @Parametros where id = @id 
110
111    end