Generate Archiving Script

————GENERATE ARCHIVING SCRIPT———————-
declare @dataBase varchar(1000)=’WBSHG_ECI23DC46EB’
IF Exists  (select name from sysobjects  where xtype = ‘U’ and name=’dbtables’)
drop table dbtables

–All Tables
select ROW_NUMBER() OVER (ORDER BY name)  Incmnt_id,name as TblName  into dbtables
from sysobjects
where xtype = ‘U’

–All Columns
declare @query nvarchar(max)

declare @startpoint int=1
declare @endpoint int
DECLARE @List nvarchar(max)
DECLARE @ColumnList nvarchar(max)
declare @TableName varchar(1000)

set @endpoint=(select MAX(Incmnt_id) from dbtables)

while (@startpoint <=@endpoint)
begin
set @TableName=(select TblName   from dbtables where Incmnt_id=@startpoint
and TblName not in (‘dbtables’,’BANK’,
‘BANK_BRANCH’,’BeneficiaryRaw’,’Block’,’CardDetail’,’CardWastageLog’,’CardWastageReason’,’Category’,’DataBackupLog’,’DependentRaw’,
‘District’,’ECIApplicationDetail’,’Education’,’Finger’,’FingerAuthenticationLog’,’IdType’,’InsuranceDetail’,
‘IssunceActivityLog’,’MajorActivity’,’Operator’,’OperatorFingerprint’,’PanchayatTown’,’Relation’,’Role’,
‘State’,’sysdiagrams’,’Village’))

set @List=(Select ‘[‘ +column_name + ‘],’ AS [text()] From information_schema.columns
where table_name = @TableName
–Exclude Incremntal column
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, ‘IsIdentity’) <>1
order by ordinal_position For XML PATH (”))
SET @ColumnList = SUBSTRING(@List, 1, Len(@List) – 1)

set @query=’insert into ‘+@TableName+’  (‘+ @ColumnList +’ )
select ‘+ @ColumnList +’ from ‘+@dataBase+’.dbo.’+@TableName+’ ‘
print(@query)
set @startpoint=@startpoint +1
end

—-DROP PRIMARY AND FOREIGN KEY
/*
select  ‘ALTER TABLE ‘ + Tblname + ‘ DROP CONSTRAINT ‘ + name
from sysobjects as o inner join (
select id,name as Tblname from sysobjects where
name not in (‘dbtables’,’BANK’,
‘BANK_BRANCH’,’BeneficiaryRaw’,’Block’,’CardDetail’,’CardWastageLog’,’CardWastageReason’,’Category’,’DataBackupLog’,’DependentRaw’,
‘District’,’ECIApplicationDetail’,’Education’,’Finger’,’FingerAuthenticationLog’,’IdType’,’InsuranceDetail’,
‘IssunceActivityLog’,’MajorActivity’,’Operator’,’OperatorFingerprint’,’PanchayatTown’,’Relation’,’Role’,
‘State’,’sysdiagrams’,’Village’)) as t on t.id=o.parent_obj
where xtype in (‘pk’,’f’)
*/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s