tsql313213

/*
CREATE TABLE Teacher
(
login char(5),
fname varchar(30),
lname varchar(50),
email varchar(50),
department int,
primary key(login)
);


CREATE TABLE Department
(
id int IDENTITY(1,1),
name varchar(50),
head char(5) FOREIGN KEY REFERENCES Teacher(login)
primary key(id)
);


drop table teacher


ALTER TABLE Teacher
ADD FOREIGN KEY (department)
REFERENCES Department(id)
*/


/*
insert into department
values('lamy',NULL)


insert into department
values('homolove',NULL),('bukvy',NULL)
insert into department
values('borci',NULL)

insert into teacher values ('sin01','libor','sindel','sin@dsada',4),('hom10','jan','hjomola','hom@dsadas',3),('zik00','adam','ziki','zik@@@',1),('klo00','ad','klo','klo@@',4)

update department
set head='hom10'
where name = 'homolove'

update department
set head = 'zik00'
where name = 'bukvy'

update department
set head = 'klo00'
where name = 'borci'

*/
/*
ALTER PROCEDURE PrintReport
AS
DECLARE @login char(5)
DECLARE @fname varchar(30)
DECLARE @lname varchar(50)
DECLARE @email varchar(50)
DECLARE @department int
DECLARE teachers cursor for (
select * from teacher
where department in
(
SELECT department from
(
SELECT department, count(department) as pocet_ucitelu from teacher t join department d on t.department = d.id group by department
)tmp
where tmp.pocet_ucitelu > 1)
)
BEGIN
open teachers
fetch next from teachers
into @login,@fname,@lname,@email,@department
while @@FETCH_STATUS = 0
	begin
		print(@login + ' ' + @fname + ' ' + @lname + ' ' + @email + ' ' + cast(@department as varchar) )
		fetch next from teachers into @login,@fname,@lname,@email,@department
	end
close teachers
deallocate teachers
END

exec PrintReport
*/


ALTER PROCEDURE CopyTableDate(@tabulka varchar(50))
AS

DECLARE c_column_list CURSOR for (SELECT COLUMN_name, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @tabulka)
DECLARE @s varchar(500)


DECLARE @column_name varchar(50)
DECLARE @data_type varchar(50)
DECLARE @length int

DECLARE @count int
DECLARE @c varchar
DECLARE @tab varchar(30)
BEGIN
set @count = @@rowcount
set @tab = @tabulka + '_dcp ('
set @s = 'CREATE TABLE' + @tab
open c_column_list
fetch next from c_column_list
into @column_name, @data_type, @length
set @s = @s + @column_name + ' ' + @data_type + '(' + cast(@length as varchar) + ')'

while @@FETCH_STATUS = 0
	begin
		fetch next from c_column_list into @column_name, @data_type, @length
		set @s = @s + ','+@column_name + ' ' + @data_type + '(' + cast(@length as varchar) + ')'
	end
close c_column_list
deallocate c_column_list

set @s = @s + ');'
print @s

END


execute CopyTableDate 'Teacher'