copy of existing table and data

Hello,
SELECT * INTO NEW_TABLE FROM OLD_TABLE
THis will copy the table structure and data into NEW_TABLE. You may need to
craete the Indexes manually to NEW_TABLE.
Thanks
Hari
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
> My query is very simple, I am new to SQL.
> I want to create copy of existing table and data.
> Pls suggest a command !
> Thanks in advance
> Sanjay
>
Also...
As well as indexes Primary Keys, Foreign Keys, CHECK constraints are not
transferred, but Identities are!!! E.g
CREATE TABLE MyMaster ( id int not null identity constraint PK_MyMaster
PRIMARY KEY,
Value int not null )
CREATE TABLE Mydetail (
id int not null identity constraint PK_Mydetail PRIMARY KEY,
master_id int not null constraint FK_MyMaster FOREIGN KEY REFERENCES
MyMaster ( id ),
Value int not null CONSTRAINT CK_value CHECK ( value > 10 ))
INSERT INTO MyMaster ( value )
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
INSERT INTO Mydetail ( Master_id, value )
SELECT 1, 100
UNION ALL SELECT 2, 20
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 40
UNION ALL SELECT 4, 400
SELECT * INTO MyOtherMaster FROM MyMaster
EXEC sp_help MyMaster
EXEC sp_help MyOtherMaster
SELECT * INTO MyOtherDetail FROM MyDetail
EXEC sp_help MyDetail
EXEC sp_help MyOtherDetail
John
"Hari Prasad" wrote:

> Hello,
> SELECT * INTO NEW_TABLE FROM OLD_TABLE
> THis will copy the table structure and data into NEW_TABLE. You may need to
> craete the Indexes manually to NEW_TABLE.
> Thanks
> Hari
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23n7D6speHHA.3960@.TK2MSFTNGP02.phx.gbl...
>
>

Copy just the Database Structure not the Data

I have been working on my windows app, using SQL Server 2005 (& C#) but I now have loads of junk data, so I want to copy the structure without any data to a new set of files that can become the production version.

I assume I can use backup and restore in future when I want a copy of my production data to use for future testing.

How would I take just one table from my test database and add it to my production database without retyping the design?

Apart from the size limitation is there any advantage in buying a version of SQL Server over using the Express edition?

hi,

GrahamY wrote:

I have been working on my windows app, using SQL Server 2005 (& C#) but I now have loads of junk data, so I want to copy the structure without any data to a new set of files that can become the production version.

you can script out the metadata using SQL Server Management Studio Express, but this still works for 1 object at the time...

or you can have a look at this toolkit for a more comprehensive generation "wizard"..

or, you can have a look at 2 free prjs of mines, amScript and amInsert that can produce DDL scripts as long as INSERT INTO scripts to populate existing tables...

then you can execute the DDL script to create a db "clone"....

I assume I can use backup and restore in future when I want a copy of my production data to use for future testing.

backup/restore feature can be handy, but not if you want to only have an "empty" database..

How would I take just one table from my test database and add it to my production database without retyping the design?

see above..

Apart from the size limitation is there any advantage in buying a version of SQL Server over using the Express edition?

the full blown editions offer other "features" missing in Express editions as the complete SSIS integration, BI tools and features, mirroring features, more cpu support, more ram support ... http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

regards

copy fields of one table to the field structure of another table

How can I copy fields (not records) from one table to the field structure of
another table (in the same database on a sql server 2000)? So that I have
not to re-type the name and properties of the fields and to be sure the name
and properties of the fields are the same in both tables.
Thanks,
HugoScript Table definition and create new table with different name.
In Query Analizer right click on table -> Script Object to New Window As ->
Create. Type new name for table and run script.
"Educo Gent" wrote:

> How can I copy fields (not records) from one table to the field structure
of
> another table (in the same database on a sql server 2000)? So that I have
> not to re-type the name and properties of the fields and to be sure the na
me
> and properties of the fields are the same in both tables.
> Thanks,
> Hugo
>
>|||SELECT * FROM SourceTable
Into DestinationTable WHERE 1=0
Remeber that it will not copy the keys and indexes
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Educo Gent" <educo.gent@.skynet.be> wrote in message
news:ndHOd.8101$Ll6.561275@.phobos.telenet-ops.be...
> How can I copy fields (not records) from one table to the field structure
> of another table (in the same database on a sql server 2000)? So that I
> have not to re-type the name and properties of the fields and to be sure
> the name and properties of the fields are the same in both tables.
> Thanks,
> Hugo
>|||Hi Roji
The syntax is wrong
> SELECT * FROM SourceTable
> Into DestinationTable WHERE 1=0
SELECT * INTO Dest FROM Source WHERE 1=0
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:O0bvQS2DFHA.1292@.TK2MSFTNGP10.phx.gbl...
> SELECT * FROM SourceTable
> Into DestinationTable WHERE 1=0
> Remeber that it will not copy the keys and indexes
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Educo Gent" <educo.gent@.skynet.be> wrote in message
> news:ndHOd.8101$Ll6.561275@.phobos.telenet-ops.be...
structure
>|||You are right Uri. Thanks
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23QWD0W2DFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi Roji
> The syntax is wrong
>
> SELECT * INTO Dest FROM Source WHERE 1=0
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:O0bvQS2DFHA.1292@.TK2MSFTNGP10.phx.gbl...
> structure
>

copy db table from one db to another

how do I copy a table, structure only from one database to another? I select copy in the table view of one database, but then paste is not available to me in table view of the other database. I am transitioning two db tables from a development web server to the production server, in order to go live... I don't want to have to type all of the design in the new table dialog!

Please help quickly!From Enterprise Manager if you left-click the tablename and under the menu option All Tasks you will see anoth menu option called Emport Data... . This will allow you to export the data and the table structure (if the destination is SQL Server).|||the quickest way to do it is to go into enterprise manager, and click (single click) on the table that must have its structure copied. press Ctrl-C (ie copy), open query analyser, select the database in which the new table must be created and press Ctrl-V (ie paste). The sql code for creating the table should now appear.

This works in SQL Server 7. I have not tried it in 2000.|||For Generating SQL Script:
1. You can select whatever tables you want in Enterprise manager
2. Right click and then in "All Tasks" select "Generate SQL Script"

This is the correct way of generating SQL Script for any objects.

Have fun.

Varad01

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye
1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options).
3. Create your destination db.
4. Run the generated script.
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:

> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>
|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:

> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
>
>

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options).
3. Create your destination db.
4. Run the generated script.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:
> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I would like to copy the structure of a DB (tables,relations, indexes,
> > default values, ecc...) into a new DB on the same server.
> >
> > I don't want to copy the data in the tables. Is this possible? How?
> >
> > Thank you very much
> >
> > Bye
> >
> >
>
>

Copy DB structure into a new DB

Hello,
I would like to copy the structure of a DB (tables,relations, indexes,
default values, ecc...) into a new DB on the same server.
I don't want to copy the data in the tables. Is this possible? How?
Thank you very much
Bye1. Right click on your source database in Enterprise Manager-->All
Tasks-->Generate SQL Scripts.
2. You can then choose what objects you need to be scripted (lots of options
).
3. Create your destination db.
4. Run the generated script.
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"news.microsoft.com" wrote:

> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>
>|||Use DTS...
Choose the "Copy objects and data between SQL Server databases"
Here is the key... on the next screen, uncheck "Copy Data"
"news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
> Hello,
> I would like to copy the structure of a DB (tables,relations, indexes,
> default values, ecc...) into a new DB on the same server.
> I don't want to copy the data in the tables. Is this possible? How?
> Thank you very much
> Bye
>|||If you use the "Generate SQL Script" function, it won't generate the "DROP"
statements for users and logins though. I wonder if they'll get that fixed
anytime soon?
"Armando Prato" wrote:

> Use DTS...
> Choose the "Copy objects and data between SQL Server databases"
> Here is the key... on the next screen, uncheck "Copy Data"
>
> "news.microsoft.com" <diego.lotti@.NOSPAMiol.it> wrote in message
> news:uaTGhRpwEHA.2908@.tk2msftngp13.phx.gbl...
>
>

Copy database to use for developement

I have recently made the migration to SQL from Access. I have been using a
database for the developement phase but now I need to copy the structure and
use it for the final product. I want to leave the existing database to use
for further development. What is the best route to copy and paste a database
from within the server? It will be only a copy of the structure, no data at
all. Thanks
For SQL Server 2000, use Enterprise Manager to generate the script.
Right-click on the database, select All Tasks --> Generate SQL Script.
In the Generate SQL Scripts dialog, click the Show All button and select the
objects (tables, stored procedure, functions, etc.) that you want to generate
scripts for. In the Options tab, select Script Database and any indexes and
keys you want to script. You can also script users, roles, etc.
Then run, or have the DBA run, this script on the target server.
"AkAlan" wrote:

> I have recently made the migration to SQL from Access. I have been using a
> database for the developement phase but now I need to copy the structure and
> use it for the final product. I want to leave the existing database to use
> for further development. What is the best route to copy and paste a database
> from within the server? It will be only a copy of the structure, no data at
> all. Thanks
sqlsql

Copy database to use for developement

I have recently made the migration to SQL from Access. I have been using a
database for the developement phase but now I need to copy the structure and
use it for the final product. I want to leave the existing database to use
for further development. What is the best route to copy and paste a databas
e
from within the server? It will be only a copy of the structure, no data at
all. ThanksFor SQL Server 2000, use Enterprise Manager to generate the script.
Right-click on the database, select All Tasks --> Generate SQL Script.
In the Generate SQL Scripts dialog, click the Show All button and select the
objects (tables, stored procedure, functions, etc.) that you want to generat
e
scripts for. In the Options tab, select Script Database and any indexes and
keys you want to script. You can also script users, roles, etc.
Then run, or have the DBA run, this script on the target server.
"AkAlan" wrote:

> I have recently made the migration to SQL from Access. I have been using a
> database for the developement phase but now I need to copy the structure a
nd
> use it for the final product. I want to leave the existing database to use
> for further development. What is the best route to copy and paste a datab
ase
> from within the server? It will be only a copy of the structure, no data a
t
> all. Thanks

Copy database to use for developement

I have recently made the migration to SQL from Access. I have been using a
database for the developement phase but now I need to copy the structure and
use it for the final product. I want to leave the existing database to use
for further development. What is the best route to copy and paste a database
from within the server? It will be only a copy of the structure, no data at
all. ThanksFor SQL Server 2000, use Enterprise Manager to generate the script.
Right-click on the database, select All Tasks --> Generate SQL Script.
In the Generate SQL Scripts dialog, click the Show All button and select the
objects (tables, stored procedure, functions, etc.) that you want to generate
scripts for. In the Options tab, select Script Database and any indexes and
keys you want to script. You can also script users, roles, etc.
Then run, or have the DBA run, this script on the target server.
"AkAlan" wrote:
> I have recently made the migration to SQL from Access. I have been using a
> database for the developement phase but now I need to copy the structure and
> use it for the final product. I want to leave the existing database to use
> for further development. What is the best route to copy and paste a database
> from within the server? It will be only a copy of the structure, no data at
> all. Thanks

copy database structure only

Yes, script the database. In SQL Server 2005 rigth-click your database,
select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Neil" wrote:

> Is there a way to copy a database without all the data? I want to end up
> with the same database but "empty".
> Thanks
>
>Great. Thank you.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:9981EBA5-00B0-4303-96C4-476485D6CF94@.microsoft.com...[vbcol=seagreen]
> Yes, script the database. In SQL Server 2005 rigth-click your database,
> select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Neil" wrote:
>|||Is there a way to copy a database without all the data? I want to end up
with the same database but "empty".
Thanks|||Yes, script the database. In SQL Server 2005 rigth-click your database,
select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Neil" wrote:

> Is there a way to copy a database without all the data? I want to end up
> with the same database but "empty".
> Thanks
>
>|||Great. Thank you.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:9981EBA5-00B0-4303-96C4-476485D6CF94@.microsoft.com...[vbcol=seagreen]
> Yes, script the database. In SQL Server 2005 rigth-click your database,
> select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Neil" wrote:
>

copy database structure only

Is there a way to copy a database without all the data? I want to end up
with the same database but "empty".
ThanksYes, script the database. In SQL Server 2005 rigth-click your database,
select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Neil" wrote:
> Is there a way to copy a database without all the data? I want to end up
> with the same database but "empty".
> Thanks
>
>|||Great. Thank you.
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:9981EBA5-00B0-4303-96C4-476485D6CF94@.microsoft.com...
> Yes, script the database. In SQL Server 2005 rigth-click your database,
> select Tasks and Generate Scripts to run the SQL Server Scripts Wizard.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Neil" wrote:
> > Is there a way to copy a database without all the data? I want to end up
> > with the same database but "empty".
> >
> > Thanks
> >
> >
> >

Copy database roles between databases sql server 2005

Hi There,
This is vexing to say the least.
Setup:
Single SQL 2005 standard server: 2 databases, with the same table
structure and query structure.
Table Count: 78
Query Count: 3
Database A has 8 customized security roles, with different permissions
across all of the tables.
Database B has the same table structure, but different data, and none
of the roles defined in A.
How do I copy the roles from Database A to Database B?
To do it by hand would be error prone, and not much fun...
Someone posted the following in another forum, but I can't get it to
work, says that there is an invalid connection:
- Execute batch in the old DB
-- Execute result in new DB
-- Script permissions on all tables
-- Author: Th. Fuchs, IMC GmbH Chemnitz
declare @.object int, @.hresult int, @.property varchar(255), @.return
varchar(8000)
declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
declare @.ScriptType integer, @.tabname varchar(200), @.dbname
varchar(128), @.pwd varchar(20)
declare @.tablelist table (tabid integer, tabname varchar(128))
set @.dbname = 'INVEKOS2' -- define db to script
set @.pwd = '' -- top secret!
-- Create the sqlserver-object
execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
if @.hresult = 0 -- connect to server
execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
'SMUL-DB-121', 'sa', @.pwd
-- Get all tablenames
insert into @.tablelist(tabid, tabname)
select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
object_name(id)
from dbo.sysobjects
where objectproperty(id, 'IsTable') = 1
and objectproperty(id, 'IsSystemTable') = 0
-- step through tables, script descriped in
http://msdn.microsoft.com/library/de...f_m_s_5e2a.asp
select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
select tabname from @.tablelist order by tabid
open cur_tab
fetch next from cur_tab into @.tabname
while @.@.fetch_status = 0 and @.hresult = 0
begin
select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
'").script'
execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
@.ScriptType
print @.return
fetch next from cur_tab into @.tabname
end
close cur_tab
deallocate cur_tab
-- Destroy the object.
if @.hresult = 0 -- disconnect and freemem
execute @.hresult = sp_OADestroy @.object
-- If Error occurs, get a tip
if @.hresult != 0
begin
execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
select hresult = convert(varbinary(4),@.hresult), Source = @.src,
Description = @.desc
end
Thanks for your time.
Perhaps one of these articles will give you the information you desire.
http://www.sqlservercentral.com/scri...tions/1598.asp Script Roles
and Permissions
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<manganb@.gmail.com> wrote in message
news:1159562833.316536.262150@.m73g2000cwd.googlegr oups.com...
> Hi There,
> This is vexing to say the least.
> Setup:
> Single SQL 2005 standard server: 2 databases, with the same table
> structure and query structure.
> Table Count: 78
> Query Count: 3
>
> Database A has 8 customized security roles, with different permissions
> across all of the tables.
> Database B has the same table structure, but different data, and none
> of the roles defined in A.
> How do I copy the roles from Database A to Database B?
> To do it by hand would be error prone, and not much fun...
> Someone posted the following in another forum, but I can't get it to
> work, says that there is an invalid connection:
> - Execute batch in the old DB
> -- Execute result in new DB
> -- Script permissions on all tables
> -- Author: Th. Fuchs, IMC GmbH Chemnitz
> declare @.object int, @.hresult int, @.property varchar(255), @.return
> varchar(8000)
> declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
> declare @.ScriptType integer, @.tabname varchar(200), @.dbname
> varchar(128), @.pwd varchar(20)
> declare @.tablelist table (tabid integer, tabname varchar(128))
> set @.dbname = 'INVEKOS2' -- define db to script
> set @.pwd = '' -- top secret!
> -- Create the sqlserver-object
> execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
> if @.hresult = 0 -- connect to server
> execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
> 'SMUL-DB-121', 'sa', @.pwd
> -- Get all tablenames
> insert into @.tablelist(tabid, tabname)
> select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
> object_name(id)
> from dbo.sysobjects
> where objectproperty(id, 'IsTable') = 1
> and objectproperty(id, 'IsSystemTable') = 0
> -- step through tables, script descriped in
> --
> http://msdn.microsoft.com/library/de...f_m_s_5e2a.asp
> select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
> declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
> select tabname from @.tablelist order by tabid
> open cur_tab
> fetch next from cur_tab into @.tabname
> while @.@.fetch_status = 0 and @.hresult = 0
> begin
> select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
> '").script'
> execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
> @.ScriptType
> print @.return
> fetch next from cur_tab into @.tabname
> end
> close cur_tab
> deallocate cur_tab
> -- Destroy the object.
> if @.hresult = 0 -- disconnect and freemem
> execute @.hresult = sp_OADestroy @.object
> -- If Error occurs, get a tip
> if @.hresult != 0
> begin
> execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> select hresult = convert(varbinary(4),@.hresult), Source = @.src,
> Description = @.desc
> end
> Thanks for your time.
>

Copy database roles between databases sql server 2005

Hi There,
This is vexing to say the least.
Setup:
Single SQL 2005 standard server: 2 databases, with the same table
structure and query structure.
Table Count: 78
Query Count: 3
Database A has 8 customized security roles, with different permissions
across all of the tables.
Database B has the same table structure, but different data, and none
of the roles defined in A.
How do I copy the roles from Database A to Database B?
To do it by hand would be error prone, and not much fun...
Someone posted the following in another forum, but I can't get it to
work, says that there is an invalid connection:
- Execute batch in the old DB
-- Execute result in new DB
-- Script permissions on all tables
-- Author: Th. Fuchs, IMC GmbH Chemnitz
declare @.object int, @.hresult int, @.property varchar(255), @.return
varchar(8000)
declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
declare @.ScriptType integer, @.tabname varchar(200), @.dbname
varchar(128), @.pwd varchar(20)
declare @.tablelist table (tabid integer, tabname varchar(128))
set @.dbname = 'INVEKOS2' -- define db to script
set @.pwd = '' -- top secret!
-- Create the sqlserver-object
execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
if @.hresult = 0 -- connect to server
execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
'SMUL-DB-121', 'sa', @.pwd
-- Get all tablenames
insert into @.tablelist(tabid, tabname)
select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
object_name(id)
from dbo.sysobjects
where objectproperty(id, 'IsTable') = 1
and objectproperty(id, 'IsSystemTable') = 0
-- step through tables, script descriped in
--
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_s_5e2a.asp
select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
select tabname from @.tablelist order by tabid
open cur_tab
fetch next from cur_tab into @.tabname
while @.@.fetch_status = 0 and @.hresult = 0
begin
select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
'").script'
execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
@.ScriptType
print @.return
fetch next from cur_tab into @.tabname
end
close cur_tab
deallocate cur_tab
-- Destroy the object.
if @.hresult = 0 -- disconnect and freemem
execute @.hresult = sp_OADestroy @.object
-- If Error occurs, get a tip
if @.hresult != 0
begin
execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
select hresult = convert(varbinary(4),@.hresult), Source = @.src,
Description = @.desc
end
Thanks for your time.Perhaps one of these articles will give you the information you desire.
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script Roles
and Permissions
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<manganb@.gmail.com> wrote in message
news:1159562833.316536.262150@.m73g2000cwd.googlegroups.com...
> Hi There,
> This is vexing to say the least.
> Setup:
> Single SQL 2005 standard server: 2 databases, with the same table
> structure and query structure.
> Table Count: 78
> Query Count: 3
>
> Database A has 8 customized security roles, with different permissions
> across all of the tables.
> Database B has the same table structure, but different data, and none
> of the roles defined in A.
> How do I copy the roles from Database A to Database B?
> To do it by hand would be error prone, and not much fun...
> Someone posted the following in another forum, but I can't get it to
> work, says that there is an invalid connection:
> - Execute batch in the old DB
> -- Execute result in new DB
> -- Script permissions on all tables
> -- Author: Th. Fuchs, IMC GmbH Chemnitz
> declare @.object int, @.hresult int, @.property varchar(255), @.return
> varchar(8000)
> declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
> declare @.ScriptType integer, @.tabname varchar(200), @.dbname
> varchar(128), @.pwd varchar(20)
> declare @.tablelist table (tabid integer, tabname varchar(128))
> set @.dbname = 'INVEKOS2' -- define db to script
> set @.pwd = '' -- top secret!
> -- Create the sqlserver-object
> execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
> if @.hresult = 0 -- connect to server
> execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
> 'SMUL-DB-121', 'sa', @.pwd
> -- Get all tablenames
> insert into @.tablelist(tabid, tabname)
> select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
> object_name(id)
> from dbo.sysobjects
> where objectproperty(id, 'IsTable') = 1
> and objectproperty(id, 'IsSystemTable') = 0
> -- step through tables, script descriped in
> --
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_s_5e2a.asp
> select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
> declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
> select tabname from @.tablelist order by tabid
> open cur_tab
> fetch next from cur_tab into @.tabname
> while @.@.fetch_status = 0 and @.hresult = 0
> begin
> select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
> '").script'
> execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
> @.ScriptType
> print @.return
> fetch next from cur_tab into @.tabname
> end
> close cur_tab
> deallocate cur_tab
> -- Destroy the object.
> if @.hresult = 0 -- disconnect and freemem
> execute @.hresult = sp_OADestroy @.object
> -- If Error occurs, get a tip
> if @.hresult != 0
> begin
> execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> select hresult = convert(varbinary(4),@.hresult), Source = @.src,
> Description = @.desc
> end
> Thanks for your time.
>

Copy database roles between databases sql server 2005

Hi There,
This is vexing to say the least.
Setup:
Single SQL 2005 standard server: 2 databases, with the same table
structure and query structure.
Table Count: 78
Query Count: 3
Database A has 8 customized security roles, with different permissions
across all of the tables.
Database B has the same table structure, but different data, and none
of the roles defined in A.
How do I copy the roles from Database A to Database B?
To do it by hand would be error prone, and not much fun...
Someone posted the following in another forum, but I can't get it to
work, says that there is an invalid connection:
- Execute batch in the old DB
-- Execute result in new DB
-- Script permissions on all tables
-- Author: Th. Fuchs, IMC GmbH Chemnitz
declare @.object int, @.hresult int, @.property varchar(255), @.return
varchar(8000)
declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
declare @.ScriptType integer, @.tabname varchar(200), @.dbname
varchar(128), @.pwd varchar(20)
declare @.tablelist table (tabid integer, tabname varchar(128))
set @.dbname = 'INVEKOS2' -- define db to script
set @.pwd = '' -- top secret!
-- Create the sqlserver-object
execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
if @.hresult = 0 -- connect to server
execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
'SMUL-DB-121', 'sa', @.pwd
-- Get all tablenames
insert into @.tablelist(tabid, tabname)
select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
object_name(id)
from dbo.sysobjects
where objectproperty(id, 'IsTable') = 1
and objectproperty(id, 'IsSystemTable') = 0
-- step through tables, script descriped in
--
http://msdn.microsoft.com/library/d...r />
_5e2a.asp
select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
select tabname from @.tablelist order by tabid
open cur_tab
fetch next from cur_tab into @.tabname
while @.@.fetch_status = 0 and @.hresult = 0
begin
select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
'").script'
execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
@.ScriptType
print @.return
fetch next from cur_tab into @.tabname
end
close cur_tab
deallocate cur_tab
-- Destroy the object.
if @.hresult = 0 -- disconnect and freemem
execute @.hresult = sp_OADestroy @.object
-- If Error occurs, get a tip
if @.hresult != 0
begin
execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
select hresult = convert(varbinary(4),@.hresult), Source = @.src,
Description = @.desc
end
Thanks for your time.Perhaps one of these articles will give you the information you desire.
http://www.sqlservercentral.com/scr...utions/1598.asp Script Roles
and Permissions
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/kb/274188 Troubleshooting Orphan Logins
http://www.support.microsoft.com/?id=240872 Resolve Permission
Issues -Database Is Moved Between SQL Servers
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<manganb@.gmail.com> wrote in message
news:1159562833.316536.262150@.m73g2000cwd.googlegroups.com...
> Hi There,
> This is vexing to say the least.
> Setup:
> Single SQL 2005 standard server: 2 databases, with the same table
> structure and query structure.
> Table Count: 78
> Query Count: 3
>
> Database A has 8 customized security roles, with different permissions
> across all of the tables.
> Database B has the same table structure, but different data, and none
> of the roles defined in A.
> How do I copy the roles from Database A to Database B?
> To do it by hand would be error prone, and not much fun...
> Someone posted the following in another forum, but I can't get it to
> work, says that there is an invalid connection:
> - Execute batch in the old DB
> -- Execute result in new DB
> -- Script permissions on all tables
> -- Author: Th. Fuchs, IMC GmbH Chemnitz
> declare @.object int, @.hresult int, @.property varchar(255), @.return
> varchar(8000)
> declare @.src varchar(255), @.desc varchar(255), @.cmd varchar(300)
> declare @.ScriptType integer, @.tabname varchar(200), @.dbname
> varchar(128), @.pwd varchar(20)
> declare @.tablelist table (tabid integer, tabname varchar(128))
> set @.dbname = 'INVEKOS2' -- define db to script
> set @.pwd = '' -- top secret!
> -- Create the sqlserver-object
> execute @.hresult = sp_OACreate 'SQLDMO.SQLServer', @.object output
> if @.hresult = 0 -- connect to server
> execute @.hresult = sp_OAMethod @.object, 'Connect', NULL,
> 'SMUL-DB-121', 'sa', @.pwd
> -- Get all tablenames
> insert into @.tablelist(tabid, tabname)
> select id, user_name(objectproperty ( id , 'OwnerId')) + '.' +
> object_name(id)
> from dbo.sysobjects
> where objectproperty(id, 'IsTable') = 1
> and objectproperty(id, 'IsSystemTable') = 0
> -- step through tables, script descriped in
> --
> http://msdn.microsoft.com/library/d.../>
_s_5e2a.asp
> select @.ScriptType = 2 -- SQLDMOScript_ObjectPermissions
> declare cur_tab CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC for
> select tabname from @.tablelist order by tabid
> open cur_tab
> fetch next from cur_tab into @.tabname
> while @.@.fetch_status = 0 and @.hresult = 0
> begin
> select @.cmd = 'databases("' + @.dbname + '").tables("' + @.tabname +
> '").script'
> execute @.hresult = sp_OAMethod @.object, @.cmd, @.return OUTPUT,
> @.ScriptType
> print @.return
> fetch next from cur_tab into @.tabname
> end
> close cur_tab
> deallocate cur_tab
> -- Destroy the object.
> if @.hresult = 0 -- disconnect and freemem
> execute @.hresult = sp_OADestroy @.object
> -- If Error occurs, get a tip
> if @.hresult != 0
> begin
> execute sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> select hresult = convert(varbinary(4),@.hresult), Source = @.src,
> Description = @.desc
> end
> Thanks for your time.
>

Copy database

Hallo !!!
I have MS SQL Server 2000
How I can copy database but only structure (without data).
allways when I copy this structure of database then I see "Failed to copy
from ms sql server to ms sql server"
WHY ?
pawelThis was answered in .programming, try not to multi-post.
Ray Higdon MCSE, MCDBA, CCNA
--
"Pawel" <admin@.channoine.pl> wrote in message
news:eOpsKoy8DHA.3008@.TK2MSFTNGP09.phx.gbl...
> Hallo !!!
> I have MS SQL Server 2000
> How I can copy database but only structure (without data).
> allways when I copy this structure of database then I see "Failed to copy
> from ms sql server to ms sql server"
> WHY ?
> pawel
>

Copy database

Hallo !!!
I have MS SQL Server 2000
How I can copy database but only structure (without data).
allways when I copy this structure of database then I see "Failed to copy
from ms sql server to ms sql server"
WHY ?
pawelThis was answered in .programming, try not to multi-post.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Pawel" <admin@.channoine.pl> wrote in message
news:eOpsKoy8DHA.3008@.TK2MSFTNGP09.phx.gbl...
> Hallo !!!
> I have MS SQL Server 2000
> How I can copy database but only structure (without data).
> allways when I copy this structure of database then I see "Failed to copy
> from ms sql server to ms sql server"
> WHY ?
> pawel
>

copy a table structure

hi
how can i copy a table structure from a database to another(without bakup)?Right Click on the table in Enterprise manager go, All Tasks, Generate SQL Script.

Then Click formatting tab, make sure that generate CREATE is the only script generated then...

Options, Script Indexes, Script Triggers, Script PRIMARY Keys, Foreign Keys.. Check on.

Hope this helps.|||i think this also might work:

select * into database2.table from database1.table1 where 1=0|||i want somethin like this:

CREATE table mytable_copy AS
SELECT * from originaltable ;

that works in mySQL, but in msSQL i got this error msg:

Incorrect syntax near the keyword 'as'.|||congrats for resurrecting a one year old thread

did you actually try the sql in post #3?

copy a table

Hello all!

How to copy a table in MS SQL Server 2000 without chaning a structure? I
mean, I have one table, which has autoincrement numeric field (ID). When
I copy this table by exporting this table into the same database folder
I loose the specification of the field ID. Now it is not autoincrement
field but usual int field. Is it possible to copy this table without
changing data and structure of a table?

Kindest regards
Thank you
Marcin from Poland

*** Sent via Developersdex http://www.developersdex.com ***Marcin Zmyslowski wrote:
> Hello all!
> How to copy a table in MS SQL Server 2000 without chaning a
> structure? I mean, I have one table, which has autoincrement numeric
> field (ID). When I copy this table by exporting this table into the
> same database folder I loose the specification of the field ID. Now
> it is not autoincrement field but usual int field. Is it possible to
> copy this table without changing data and structure of a table?

With DTS copy object might do the trick. Otherwise, create DDL with QA or
EM, create the new table and then copy the data with "SET IDENTITY_INSERT
your_table ON". HTH

robert|||Insert into table1(columns)
select columns from othertable

Madhivanan

Copy a diagram from a db to another db in sql server 2005

Hello,
is there a way in SQL Server 2005 to copy a diagram from a database to
another one that has exactly the
same structure?
Thank you in advance,
LauraYou need to create the support tables for diagrams in the
destination database first - you can just click on the
database diagrams node in the destination database and
select Yes when the message box comes up asking if you want
to create the objects to use database diagramming.
Then import the dbo.sysdiagrams table from the source
database.
-Sue
On Tue, 31 Jul 2007 07:14:01 -0700, Isotta
<laura.lega@.gmail.com> wrote:
>Hello,
>is there a way in SQL Server 2005 to copy a diagram from a database to
>another one that has exactly the
>same structure?
>Thank you in advance,
>Laura