June 24, 2019 at 7:04 pm
Hi,
I want to implement a logic where I need to check if the table that I'm archiving from source database is exists or it's structure changed and if Yes, then drop and recreate the table structure in archive database. Can you please advise the best way to achieve this?
June 24, 2019 at 7:42 pm
I have used below procedure and it is working fine but unable to create keys and Indexes.
declare @tablename varchar(500)
declare @sql varchar(5000)
declare @idname varchar(50)
declare @tablearchive varchar(500)
--Select all the tables which you want to make in archive
declare tableCursor cursor FAST_FORWARD FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
where table_name
--Put your condition, if you want to filter the tables
--like '%TRN_%' and charindex('Archive',table_name) = 0 and charindex('ErrorLog',table_name) = 0
--Open the cursor and iterate till end
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tablename WHILE @@FETCH_STATUS = 0
BEGIN
set @tablearchive = @tablename+'Archive'
--check for the table exists, not, create it
IF not EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME= @tablearchive)
begin
SET @sql = 'select * into ' + @tablearchive +' from '+ @tablename +' where 1=2'
EXEC(@sql)
END
--check the structure is same, if not, create it
IF exists (select column_name from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablename and column_name not in (select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablearchive))
begin
SET @sql = 'drop table ' + @tablearchive
EXEC(@sql)
SET @sql = 'select * into ' + @tablearchive +' from '+ @tablename +' where 1=2'
EXEC(@sql)
end
--Check if the table contains, identify column,if yes, then it should be handled in different way
--You cannot remove the identity column property through T-SQL
--Since the structure of both tables are same, the insert fails, as it cannot insert the identity column
--value in the archive table
IF EXISTS(SELECT * FROM information_schema.tables WHERE table_name = @tablename AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') != 0)
BEGIN
--Select the identity column name automatically
select @idname = column_name from
information_schema.columns where
columnproperty(object_id(table_name),column_name,'isidentity')=1
AND table_name = @tablearchive
--Remove the column
SET @sql = 'ALTER TABLE ' + @tablearchive + ' DROP COLUMN ' + @idname
EXEC(@sql)
--Create the column name again (not as identity)
--archive table does require identity column
SET @sql = 'ALTER TABLE ' + @tablearchive + ' ADD ' + @idname+ ' INT'
EXEC(@sql)
END
SET @sql = 'insert into ' + @tablearchive +' select * from '+ @tablename
EXEC(@sql)
FETCH NEXT FROM tableCursor INTO @tablename
END
CLOSE tableCursor
DEALLOCATE tableCursor?
June 25, 2019 at 9:23 am
Here's a script to display all indexes including the defined columns within the current database. You can easily adjust the script to filter on the required table(s) and to generate the corresponding CREATE INDEX statement.
select
SCHEMA_NAME (o.SCHEMA_ID) SchemaName
, o.name ObjectName
, i.name IndexName
, i.type_desc
, LEFT(list, ISNULL(splitter-1,len(list))) Columns
, SUBSTRING(list, indCol.splitter +1, 100) includedColumns--len(name) - splitter-1) columns
, COUNT(1) over (partition by o.object_id)
from sys.indexes i
join sys.objects o on i.object_id = o.object_id
cross apply (select
NULLIF(charindex('|',indexCols.list),0) splitter
, list
from (select cast((
select case when sc.is_included_column = 1 and sc.ColPos = 1 then '|' else '' end +
case when sc.ColPos > 1 then ', ' else '' end + name
from (select sc.is_included_column, index_column_id, name
, ROW_NUMBER() over (partition by sc.is_included_column
order by sc.index_column_id) ColPos
from sys.index_columns sc
join sys.columns c on sc.object_id = c.object_id
and sc.column_id = c.column_id
where sc.index_id = i.index_id
and sc.object_id = i.object_id ) sc
order by sc.is_included_column
,ColPos
for xml path (''), type) as varchar(max)) list)indexCols ) indCol
--where
--i.name like '%PRIMARYKEY%'
order by
SchemaName
, ObjectName
, IndexName
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply