if exists(select 1 from SysObjects where xtype = 'P' and name = 'sp_Insert')
drop proc sp_Insert
Go
--Ctl + 4
CREATE proc sp_Insert
@table varchar(100)
as
declare @str varchar(8000)
set @str='insert '+@table+'('
--select * from syscolumns where id=object_id('sfcprwipd_mstr')
select @str=@str+[name]+',' from syscolumns where id=object_id(@table)
order by colorder
set @str=left(@str,len(@str)-1)+')'
print @str
Go
if exists(select 1 from SysObjects where xtype = 'P' and name = 'sp_Select')
drop proc sp_Select
Go
--Ctl + 3
Create proc sp_Select
@table varchar(100)
as
exec('select * from '+@table)
Go
if exists(select 1 from SysObjects where xtype = 'P' and name = 'sp_TableConstruction')
drop proc sp_TableConstruction
Go
--Ctl + 5
Create proc sp_TableConstruction
@table varchar(100)
as
declare @tmp table
(
Column_name varchar(50),
Type varchar(50),
Lenght int,
Scale varchar(5),
Nullable varchar(1),
Defaults varchar(4000),
PrimaryKey varchar(1)
)
insert @tmp (Column_name, Type, Lenght, Scale, Nullable, Defaults,PrimaryKey)
select a.name, c.name, a.length, case when c.name <> 'datetime' then isnull(a.scale,'') else '' end,
case a.isnullable when 0 then 'N' else 'Y' end, isnull(d.text,''), case when x.PrimaryKey is null then '' else x.PrimaryKey end
from SysColumns a with(nolock)
inner join (select * from SysObjects with(nolock) where xtype = 'U' and id = object_id(@table)) b on a.id = b.id
inner join SysTypes c with(nolock) on a.xtype = c.xusertype
left join syscomments d with(nolock) on a.cdefault = d.id
left join
(select f.id, colid, 'Y' as PrimaryKey from SysIndexKeys f with(nolock), SysIndexes e, SysObjects g
where f.id = e.id and f.indid = e.indid and f.id = g.parent_obj and e.name = g.name
and g.xtype = 'PK' and g. parent_obj = object_id(@table)) x on a.id = x.id and a.colid = x.colid
update @tmp set Scale = '' where Scale = '0'
select * from @tmp
Go