April 15, 2009 at 12:08 pm
Does anyone know of a system procedure or have a stored procedure that would read the definition of a table on DATABASE1, create a script so that i could then CREATE the table on DATABASE2.
Or if you have any other ideas they would be appreciated.
April 15, 2009 at 12:11 pm
ApexSQL and Redgate both sell products that will do that for you. It's not a simple process.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 1:42 pm
do you want all the tables to be copied.
else do the following.
@sql = 'Select * ' + ' INTO ' + @TargetDb+'.'+@Schema+'.'+ @TableName + ' From '+ @TableName ' + 'Where 1 = 2 '
exec @sql
so that should work out to copy the table.
but note this will not copy the indexes and other details but this should get you up and running.
Else let me know i should be able to put together some script to get this out of the door for you.
Regards
Vinay
April 15, 2009 at 3:11 pm
Here a little snippet of a script I will publish in next weeks:
Since now this only scripts the tables. The indexes and constraints are not included.
SET NOCOUNT ON
DECLARE @check_exists BIT
DECLARE @indent VARCHAR(10)
SET @check_exists = 1
SET @indent = ' ' -- CHAR(9) for tab
DECLARE @results TABLE (id INT IDENTITY, stmt NVARCHAR(MAX))
INSERT INTO @results
SELECT '--------------------------------------------'
UNION ALL SELECT '-- TABLES'
UNION ALL SELECT '--------------------------------------------'
; WITH
x (object_id, column_id, stmt, sort) AS
(
SELECT
object_id,
0,
'-- Table: ' + name,
1
FROM sys.tables
UNION ALL
SELECT
object_id,
0,
'IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID(QUOTENAME(''' +
SCHEMA_NAME(schema_id) + ''') + ''.'' + QUOTENAME(''' + name + ''')))',
2
FROM sys.tables
WHERE @check_exists = 1
UNION ALL
SELECT
object_id,
0,
CASE WHEN @check_exists = 1 THEN @indent ELSE '' END +
'CREATE TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ' (',
3
FROM sys.tables
UNION ALL
SELECT
c.object_id,
c.column_id,
@indent +
CASE WHEN @check_exists = 1 THEN @indent ELSE '' END +
QUOTENAME(c.name) + ' ' +
QUOTENAME(st.name) +
CASE
WHEN st.name IN ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar') AND c.max_length = -1 THEN '(MAX)'
WHEN st.name IN ('binary', 'char', 'varbinary', 'varchar') THEN '(' + CONVERT(VARCHAR(10), c.max_length) + ')'
WHEN st.name IN ('nchar', 'nvarchar') THEN '(' + CONVERT(VARCHAR(10), c.max_length / 2) + ')'
WHEN st.name IN ('decimal', 'numeric') THEN '(' + CONVERT(VARCHAR(10), st.precision) + ', ' + CONVERT(VARCHAR(10), st.scale) + ')'
ELSE ''
END +
CASE WHEN c.is_filestream = 1 THEN ' FILESTREAM ' ELSE '' END +
CASE
WHEN ISNULL(c.collation_name,
CONVERT(NVARCHAR(128), DATABASEPROPERTYEX(DB_NAME(), 'Collation')))
!= CONVERT(NVARCHAR(128), DATABASEPROPERTYEX(DB_NAME(), 'Collation'))
THEN c.collation_name
ELSE ''
END +
CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
CASE WHEN c.is_identity = 1 THEN ' IDENTITY(' +
CONVERT(VARCHAR(20), IDENTITYPROPERTY(c.object_id, 'SeedValue')) +
',' +
CONVERT(VARCHAR(20), IDENTITYPROPERTY(c.object_id, 'IncrementValue')) +
')'
ELSE ''
END +
CASE WHEN c.column_id != last_column.id THEN ', ' ELSE '' END
,
4
FROM sys.columns c
JOIN sys.types st ON c.user_type_id = st.user_type_id
CROSS APPLY (SELECT MAX(column_id) id FROM sys.columns WHERE object_id = c.object_id) last_column
UNION ALL
SELECT
object_id,
0,
CASE WHEN @check_exists = 1 THEN @indent ELSE '' END +
')',
5
FROM sys.tables
UNION ALL
SELECT
t.object_id,
0,
CASE WHEN @check_exists = 1 THEN @indent ELSE '' END +
'ON ' + QUOTENAME(ds.name),
6
FROM sys.tables t
JOIN sys.indexes ix ON t.object_id = ix.object_id AND (ix.type = 1 OR ix.type = 0)
JOIN sys.data_spaces ds ON ix.data_space_id = ds.data_space_id
UNION ALL
SELECT
object_id,
0,
'',
7
FROM sys.tables
)
INSERT INTO @results
SELECT xx.stmt
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
CROSS APPLY (SELECT TOP(10000) stmt FROM x WHERE object_id = t.object_id ORDER BY sort, column_id) xx
ORDER BY s.name, t.name
INSERT INTO @results
SELECT ''
UNION ALL SELECT ''
SELECT stmt FROM @results
Greets
Flo
April 15, 2009 at 3:49 pm
Thanks Flo. I always love your posts as they are very informative and easy to understand.
I am assuming I can read the system tables to find the constraints, indexes and such, then do alter table to apply.
The thing I was going to try was to set up an SSIS package that would import the tables. This would bring over the indexes and constraints, correct?
April 15, 2009 at 3:55 pm
Hi Carol
Carol Adams (4/15/2009)
Thanks Flo. I always love your posts as they are very informative and easy to understand.
Thanks for that. Especially because I thought since now the statement is quiet ugly to understand because of no comments... :hehe:
I am assuming I can read the system tables to find the constraints, indexes and such, then do alter table to apply.
Yes you can. All information are stored within the system tables. It's sometimes a bit tricky to find the right one but it exists.
The thing I was going to try was to set up an SSIS package that would import the tables. This would bring over the indexes and constraints, correct?
Sorry, I don't know very much about SSIS since now. I don't know if SSIS does bring all the indexes, ...
Greets
Flo
April 15, 2009 at 4:08 pm
Carol Adams (4/15/2009)
Does anyone know of a system procedure or have a stored procedure that would read the definition of a table on DATABASE1, create a script so that i could then CREATE the table on DATABASE2.Or if you have any other ideas they would be appreciated.
As requested, I have another idea! Buy SQL Compare from Redgate (I have no connection with them but use the tool).
It will do the job you require but more importantly will sychronise any database you have with any other.
Frankly I have got more value out of this tool than nearly any other I ever bought!
.
May 19, 2011 at 12:13 pm
Will that create all the tables from one database to another? I need to do a bunch of test scripts and bring in the new data using new packages that I create into a different test database. Obviously I can manually create each table from the old database and server into the new database and server but if I can avoid it, I'd rather do. So the issue - many tables in database and server to be copied (structure only, not data) to new database on new server. Thanks!
Janie
May 23, 2011 at 2:17 am
Janie.Carlisle (5/19/2011)
Will that create all the tables from one database to another? I need to do a bunch of test scripts and bring in the new data using new packages that I create into a different test database. Obviously I can manually create each table from the old database and server into the new database and server but if I can avoid it, I'd rather do. So the issue - many tables in database and server to be copied (structure only, not data) to new database on new server. Thanks!Janie
It does that and a lot more. You can try a fully functional version for 14 days, so there is no risk really.
http://www.red-gate.com/products/sql-development/sql-compare/
There are other tools availabel, but I can vouch for this one.
Tim
.
May 23, 2011 at 6:26 am
Great. Will let you know how it works out. Thanks!
May 23, 2011 at 6:42 am
I do have this old script kicking around that I used for scripting Indexes and Constraints that may be of use alongside Flo's excellent post. It was only a scratch script so will not be 100% optimised and may be a little buggy, although it always worked for what I needed!
SET NOCOUNT ON
DECLARE @tablename VARCHAR(50),
@counter INT,
@obj_name VARCHAR(50),
@obj_options VARCHAR(2500),
@filename VARCHAR(50),
@obj_keys VARCHAR(2500),
@NewTableName VARCHAR(50),
@objid INT,
@fillfactor CHAR(3)
set @Newtablename = <NEW TABLE NAME>
set @tablename = <TABLE TO COPY>
set @objid = (SELECT object_ID(@tablename))
SET @filename = (select s.groupname
from sysfilegroups s, sysindexes i
where i.id = @objid
and i.indid < 2
and i.groupid = s.groupid )
--Creating the Indexes and Constraints
--indexes
IF EXISTS(SELECT name FROM tempdb..sysobjects where name like '#Indexes%')
DROP TABLE #indexes
CREATE TABLE #Indexes (Index_name varchar(250)
,Index_Desc varchar(1000)
,index_Keys varchar(4000))
EXEC('INSERT INTO #Indexes
EXEC sp_helpindex '+ @tablename +'')
SET @counter = (SELECT COUNT(*) FROM #indexes)
WHILE @counter > 0
BEGIN
SET @obj_name = (SELECT TOP 1 UPPER(index_name) from #indexes)
SET @obj_options = (SELECT UPPER(REPLACE(index_desc, ' located on '+ @filename +'', '')) FROM #indexes where index_name = @obj_Name)
SET @obj_options = (REPLACE(@obj_options, ',', ''))
SET @obj_keys = (SELECT UPPER(index_keys) FROM #indexes where index_name = @obj_Name)
PRINT '--Creating Index '+ @obj_name
SET @fillfactor = (select a.origfillfactor from sysindexes a
inner join sysobjects b
on a.id = b.id
where b.name = @tablename
and a.name = @obj_name)
--Dealing with Primary Key Creations fix applied
IF (SELECT @obj_options) NOT like '%Primary Key%'
BEGIN
IF (SELECT @obj_options) like '%unique%'
BEGIN
SET @obj_options = ('UNIQUE '+ UPPER(REPLACE(@obj_options, 'Unique', '')))
END
PRINT('CREATE '+ @obj_options +' INDEX '+ @obj_name +' ON ['+ @Newtablename +']('+ @obj_keys +') WITH FILLFACTOR = '''+ rtrim(@fillfactor) +''' ON '+ @filename +'')
END
IF (SELECT @obj_options) like '%Primary Key%' AND (SELECT @obj_options) like '%clustered%'
BEGIN
PRINT('ALTER TABLE ['+ @NewTablename +'].dbo.['+ @tablename +'] WITH CHECK ADD
CONSTRAINT [PK_'+ @Tablename +'] PRIMARY KEY CLUSTERED
(
'+ @obj_keys +'
) WITH FILLFACTOR = 100 ON [PRIMARY]')
END
IF (SELECT @obj_options) like '%Primary Key%' AND (SELECT @obj_options) NOT like '%clustered%'
BEGIN
PRINT('ALTER TABLE ['+ @Newtablename +'] WITH CHECK ADD
CONSTRAINT [PK_'+ @NewTablename +'] PRIMARY KEY
(
'+ @obj_keys +'
) WITH FILLFACTOR = 100 ON [PRIMARY]')
END
DELETE FROM #indexes where index_name = @obj_name
SET @counter = @counter -1
END
--Constraints
IF EXISTS(SELECT name FROM tempdb..sysobjects where name like '#Constraints%')
DROP TABLE #Constraints
CREATE TABLE #Constraints (con_name varchar(250)
,detail VARCHAR(250)
,test1 varchar(100)
,test2 varchar(100))
EXEC('INSERT INTO #Constraints
EXEC sp_MStablechecks N''[dbo].['+ @tablename +']''')
SET @counter = (SELECT COUNT(*) FROM #Constraints)
WHILE @counter > 0
BEGIN
SET @obj_name = (SELECT TOP 1 con_name from #constraints)
SET @obj_options = (SELECT detail FROM #constraints WHERE con_name = @obj_name)
PRINT '--Creating Constraint '+ @obj_name
PRINT('ALTER TABLE ['+ @Newtablename +'] ADD CONSTRAINT '+ @obj_name +' CHECK '+ @obj_options +'')
DELETE FROM #constraints where con_name = @obj_name
SET @counter = @counter -1
END
Of course, you could stick with the Redgate offering that I've used too and that's excellent for syncing
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply