set nocount on
go

print "set nocount on"
print "go"
print ""
go

print "declare @s varchar(32)"
print "select @s=@@servername"
print "print 'current server=%%1!',@s"
print "select @s=db_name()"
print "print 'current db=%%1!',@s"
print "print ''"
print "go"
print ""
go

declare @s varchar(32)
select @s=db_name()
print "print '-------------------------------------'"
print "print 'from server.db=%1!.%2!'",@@servername,@s
print "print '-------------------------------------'"
print "go"
print ""
go



create table #t(
param varchar(32) not null
)
go

/*----------------------- from here -------------------------------*/

insert into #t(param) values (" имя прокси ")


/*--------------------------------------------------------------*/


go



declare ccur cursor for
select
o.name as tbl_name,
user_name(o.uid) as user_name,a.char_value,a.object_info2,o.sysstat2,o.crdate
from sysobjects o,#t t, sysattributes a
where o.type="U"
and o.name=t.param
and a.object_cinfo=o.name and a.object_type='OD'
order by user_name(o.uid),o.name
go

declare
@full_name varchar(64)
go


declare
@i int,
@user_name varchar(32),
@tbl_name varchar(32),
@idcol int,
@status int,
@length int,
@col_name varchar(32),
@scale int,
@usertype int,
@type_name varchar(32),
@prec int,
@s varchar(255),
@sysstat2 int,
@extstatus int,
@place varchar(255),
@cdefault int,
@s_def varchar(255),
@crdate datetime,
@dbname varchar(32)

select @i=0
open ccur
while(1=1) begin
fetch ccur into @tbl_name,@user_name,@place,@extstatus,@sysstat2,@crdate
if @@sqlstatus<>0 break
select @i=@i+1,@dbname=db_name()
print "print '%1!.%2! [%3!] created at %4! on srv %5!.%6!' ",
@user_name,@tbl_name,@i,@crdate,@@servername,@dbname
print "if exists( select * from sysobjects o "
print " where o.name='%1!' ",@tbl_name
print " and o.uid=user_id('%1!') ",@user_name
print " and o.type='U' ) begin"
print " print '!!! Warning !!! table %1!.%2! already exists'",@user_name,@tbl_name
print " /* drop table %1!.%2! */ ",@user_name,@tbl_name
print "end"
print "else begin"

print " CREATE EXISTING TABLE %1!.%2! (",@user_name,@tbl_name

select @idcol=0
while(1=1) begin
select @idcol=c.colid,@status=c.status,@length=c.length,
@col_name=c.name,@prec=c.prec,@scale=c.scale,
@usertype=c.usertype ,@type_name=t.name ,@cdefault=c.cdefault from
syscolumns c,systypes t where
c.id=object_id(@user_name+'.'+@tbl_name) and c.usertype=t.usertype
and c.colid=@idcol+1


if @@rowcount<1 break
if @idcol=1 select @s=" "+convert(char(30),@col_name) else select @s=" , "+convert(char(30),@col_name)
if @type_name='numericn' select @type_name='numeric'
select @s=@s+" "+convert(char(30),@type_name)
if (@prec is not null)
select @s=@s+convert(char(9),' ('+convert(varchar(3),@prec)+','+convert(varchar(3),@scale)+')')
else
if @usertype in (1,2,3,4,10,24,25,26,27,28)
select @s=@s+convert(char(9),' ('+convert(varchar(3),@length)+')')
else
select @s=@s+replicate(" ",9)

if (@cdefault is not null) and (@cdefault<>0) begin
select @s_def=ltrim(rtrim(text)) from syscomments where id=@cdefault
if @@rowcount>0 select @s=@s+' '+@s_def+' '
end
if (@status & 128)=128 select @s=@s+" identity "
if (@status & 8)=8 select @s=@s+" null" else select @s=@s+" not null"
print "%1!",@s
end
print " )"

if @sysstat2&8192=8192
print "lock allpages"
if @sysstat2&16384=16384
print "lock datapages"
if @sysstat2&32768=32768
print "lock datarows"


select @s=null
select @s=s.name from sysindexes i,syssegments s where
i.id=object_id(@user_name+'.'+@tbl_name) and
i.indid>0 and i.indid<255 and (i.status &16)=16
and i.segment=s.segment
if @@rowcount=0
select @s=s.name from sysindexes i,syssegments s where
i.id=object_id(@user_name+'.'+@tbl_name) and
i.indid=0 and i.segment=s.segment

print " on '%1!'",@s
if @extstatus=1 print "external table" else print "external procedure"
print "at '%1!'",@place
print "end"
print "go"
print "if (@@error=0)"
print " print 'Ok'"
print "else "
print " print 'create table %1!.%2! Failed !!! [%3!]' ",@user_name,@tbl_name,@i
print "go"
print ""
end
close ccur
go

deallocate cursor ccur
go

print "print '----------------- grants -----------------------------'"
print ""
go


declare ccur cursor for select
(user_name(o.uid)+'.'+o.name) as tbl_name,
u.name as to_name,
v.name as what
from
sysprotects p,sysobjects o ,sysusers u ,
master..spt_values v,#t t
where
p.id=o.id and (o.type='u' )
and u.uid=p.uid
and p.action=v.number and v.type='t'
and o.name=t.param
order by user_name(o.uid),o.name,u.name

go

set nocount on
go

declare
@obj_name varchar(96),
@usr_name varchar(32),
@action varchar(30),
@i int

select @i=0

open ccur
while(1=1) begin
fetch ccur into @obj_name,@usr_name,@action
if @@sqlstatus<>0 break
select @i=@i+1
print "Grant %1! on %2! to %3!",@action ,@obj_name,@usr_name
print "if (@@error=0)"
print " print '[%1!] grant %2! on %3! to %4! succeeded.' ",
@i,@action ,@obj_name,@usr_name
print "else "
print " print '[%1!] grant %2! on %3! to %4! FAILED!' ",
@i,@action ,@obj_name,@usr_name
print "go"
print ""


end
close ccur
go
deallocate cursor ccur
go