————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’)
*/
