sql server db: indexes

Showing posts with label indexes. Show all posts

Monday, March 19, 2012

Copying Indexes from one table to another table programatically

Programatically in a stored proc, Is there anyway to copy/create all indexes
from one table to another table provided both tables have same column
deifinition? Please let me know.
On 30 May, 22:27, Sal <S...@.discussions.microsoft.com> wrote:
> Programatically in a stored proc, Is there anyway to copy/create all indexes
> from one table to another table provided both tables have same column
> deifinition? Please let me know.
Why can't you use CREATE INDEX? Script the indexes when you create the
proc.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||How can I script the indexes within the proc?
"David Portas" wrote:

> On 30 May, 22:27, Sal <S...@.discussions.microsoft.com> wrote:
> Why can't you use CREATE INDEX? Script the indexes when you create the
> proc.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Copying Indexes from one table to another table programatically

Programatically in a stored proc, Is there anyway to copy/create all indexes
from one table to another table provided both tables have same column
deifinition? Please let me know.On 30 May, 22:27, Sal <S...@.discussions.microsoft.com> wrote:
> Programatically in a stored proc, Is there anyway to copy/create all indexes
> from one table to another table provided both tables have same column
> deifinition? Please let me know.
Why can't you use CREATE INDEX? Script the indexes when you create the
proc.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||How can I script the indexes within the proc?
"David Portas" wrote:
> On 30 May, 22:27, Sal <S...@.discussions.microsoft.com> wrote:
> > Programatically in a stored proc, Is there anyway to copy/create all indexes
> > from one table to another table provided both tables have same column
> > deifinition? Please let me know.
> Why can't you use CREATE INDEX? Script the indexes when you create the
> proc.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Copying Indexes from one table to another table programatically

copying freextex indexes into multiple destination dbs on same dest server

Hi -
I've looked on Microsoft's web site:
http://support.microsoft.com/default...;EN-US;Q240867
and not yet been able to find a full answer to my question.
I'd appreciate any insight from others:
I have a database that has full text indexes on it, that I need to log ship
to multiple other (destination) servers. Also, to promote availability on
each of the destination servers, I plan to restore into two databases:
dest_db and dest_db_alt. Both are to have the same content, but different db
names. I intend to restore one set of dbs/logs from the source server into
standby mode in each destination database, sequentially, so that, with
proper client re-direction, a client hitting the destination server will
always get redirected to a read-only db in standby mode. While one db is
restoring, the other is readable. Ok, fair enough.
Now, the tricky part. The source db is freextex indexed, and one can't
build/rebuild freetext indexes on a db in standby or read-only mode. So,
apparently I'll have to copy the freetext index directory trees from the
source server and restore them on the destination server. What I'm uncertain
about is whether I'll be able to:
a) Use one set of freetext indexes files for both destination dbs (I don't
think so, If I understand things correctly),
b) Properly set the registry keys and directory/file names on the freetext
catalogs on the destination server so that even though at least one of the
destination dbs (necessarily) has a different dbid than the source database,
it will still be able to have a functioning freetext index. From the
generally related material I read it seems as if the registry keys map the
freetext catalog path(s) into SQL Server, and those catalog paths use dbids
and ftcatids integrated into their folder names to map a set of catalog
files to a dbid.
So here is my question: Can I just make multiple copies of the freetext
catalog files, differing only in the dbid portion of the folder name, and
then, using registry keys, map the newly created Freetext catalog folder(s)
into SQL Server with the relevant dbids on the destination server ?
Are dbids, or other database specific unique identifiers, hard coded within
the freetext catalog files themselves ?
Is there other important information I have to consider here ?
Thanks
Steve
Use replication for this. Create your catalogs and full text indexes using a
post snapshot script or create the tables and catalogs in advance, full text
index the tables, and then configure your article to delete the data not
drop and recreate the table (in the article properties section select the
browse button to the right of your table name, and in the snapshot tab, in
the name conflicts section , select delete all data).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Steve" <Steve_a013452@.newsgroups.nospam> wrote in message
news:OJ9F4IqGGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Hi -
> I've looked on Microsoft's web site:
> http://support.microsoft.com/default...;EN-US;Q240867
> and not yet been able to find a full answer to my question.
> I'd appreciate any insight from others:
> I have a database that has full text indexes on it, that I need to log
> ship to multiple other (destination) servers. Also, to promote
> availability on each of the destination servers, I plan to restore into
> two databases: dest_db and dest_db_alt. Both are to have the same content,
> but different db names. I intend to restore one set of dbs/logs from the
> source server into standby mode in each destination database,
> sequentially, so that, with proper client re-direction, a client hitting
> the destination server will always get redirected to a read-only db in
> standby mode. While one db is restoring, the other is readable. Ok, fair
> enough.
> Now, the tricky part. The source db is freextex indexed, and one can't
> build/rebuild freetext indexes on a db in standby or read-only mode. So,
> apparently I'll have to copy the freetext index directory trees from the
> source server and restore them on the destination server. What I'm
> uncertain about is whether I'll be able to:
> a) Use one set of freetext indexes files for both destination dbs (I don't
> think so, If I understand things correctly),
> b) Properly set the registry keys and directory/file names on the freetext
> catalogs on the destination server so that even though at least one of the
> destination dbs (necessarily) has a different dbid than the source
> database, it will still be able to have a functioning freetext index. From
> the generally related material I read it seems as if the registry keys map
> the freetext catalog path(s) into SQL Server, and those catalog paths use
> dbids and ftcatids integrated into their folder names to map a set of
> catalog files to a dbid.
> So here is my question: Can I just make multiple copies of the freetext
> catalog files, differing only in the dbid portion of the folder name, and
> then, using registry keys, map the newly created Freetext catalog
> folder(s) into SQL Server with the relevant dbids on the destination
> server ?
> Are dbids, or other database specific unique identifiers, hard coded
> within the freetext catalog files themselves ?
> Is there other important information I have to consider here ?
> Thanks
> Steve
>

Thursday, March 8, 2012

Copying data and indexes

Hi everyone!
How can I do to copy the data and the indexes from an existing table on a
database to a new table in another database?
I've tried with select * into base1.table1 from base2.table2, but it only
copy the data and not the indexes.
Can I do it by code? I'm trying to avoid the use of DTS.
Thanks.
Jaime Lucci
You would have to precreate the table on the destination server/database
with the indexes in place and then do an insert select like this
insert into base1..table1
select * from base2..table2
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jaime Lucci" <jaimelucci@.hotmail.com> wrote in message
news:Oj%23$5Yr6FHA.2816@.tk2msftngp13.phx.gbl...
> Hi everyone!
> How can I do to copy the data and the indexes from an existing table on a
> database to a new table in another database?
> I've tried with select * into base1.table1 from base2.table2, but it only
> copy the data and not the indexes.
> Can I do it by code? I'm trying to avoid the use of DTS.
> Thanks.
> Jaime Lucci
>
|||Can I "read" by code the index of the table to preceate it in the
destination database?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e25bpkr6FHA.476@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> You would have to precreate the table on the destination server/database
> with the indexes in place and then do an insert select like this
> insert into base1..table1
> select * from base2..table2
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Jaime Lucci" <jaimelucci@.hotmail.com> wrote in message
> news:Oj%23$5Yr6FHA.2816@.tk2msftngp13.phx.gbl...
a[vbcol=seagreen]
only
>
|||sp_help tablename is good. You can use the results of this to build the
index.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jaime Lucci" <jaimelucci@.hotmail.com> wrote in message
news:uK$DPor6FHA.1720@.TK2MSFTNGP09.phx.gbl...
> Can I "read" by code the index of the table to preceate it in the
> destination database?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e25bpkr6FHA.476@.TK2MSFTNGP15.phx.gbl...
> a
> only
>

Copying data and indexes

Saturday, February 25, 2012

copyind data and indexes

Friday, February 24, 2012

Copy/move database with full-text index

Is there any way to move a db with full-text indexes from one physical machine to another without having to drop catalog/indexes and the re-create them?

Cheers!

/Eskil

Hi,

From your description, it seems that you want to move a database with full indexes, right?

Full-text catalogs and indexes are not stored in a SQL Server database. Full-text catalogs and indexes are stored in separate files that the Microsoft Search service manages. You cannot use the Transact SQL statements BACKUP and RESTORE to back up and to restore full-text catalog files. After recovery or restore operations, you must separately resynchronize the full-text catalogs.

You may refer the following link which shares some solutions to move, copy, and back up full-text catalog folders and files.

http://support.microsoft.com/kb/240867

Hope it helps. Thanks.

|||

That article mate it utterly clear that I will just save my index creation statements in my snippets collection...

Thanks for helping out!

/Eskil

Sunday, February 19, 2012

Copy Tables in SQL 2k5 - Need Help

I have a database - we can call it DatabaseX and I want to copy the tables, keys and indexes to DatabaseY. I tried doing and export/import and it worked for the data but the identity settings were lost. How can I maintain the integrity of the tables and copy the data at the same time?

The Copy Database Wizard is an option when Service Pack 2 is released. Currently Service Pack 2 is available as a CTP http://www.microsoft.com/sql/ctp.mspx|||

simple way is to follow conventional Backup/Resotre method... if it is on the same machine there is no issue.. otherwise u may need to transfer the logins also....

Refer

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

also in BOL Backup database /restore database

Madhu

|||

If you want to copy objects into database X which already exists and has objects you can use bcp with the -E switch to keep identity values.

See SQL Server 2005 Books Online topic:
bcp Utility
http://msdn2.microsoft.com/en-US/library/ms162802.aspx

You can also use the SSIS Transfer Objects task.

Copy table Structure including primary keys, index etc.

Hi all,

I was wondering if there is a SQL command to copy the table structure of a table that includes primary keys, foreign keys, indexes, etc.

Thanks and have a nice day to all

Not a SQL command, but you can script this stuff out using the tools by right clicking the table, or programatically using SMO.

|||can you post a sample script or SMO please or send me a link discuss this matter thanks|||

Hi,

The easiest way to create the script is to right click the original table within SQL Server Management Studio and select "Script Table As ...\ Create To\ ..." . This will create a script for the table and its indexes.

Then you need to run the script but with the new tablename. After that, you need to copy the records using a insert/select command. (it is best to set the constraints/indexes afterwards).

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

I was thinking to use that script in my SP, On my SP I add a linked server then i want to copy all the tables exactly the same

and on the linked server the table names and table count change everyday, but i dont have any problem with that.

I was thinking if there is a way to copy exactly the same table inside an SP in that case.

Thanks

|||You can use sys tables/views inside your SP|||

can you post your samples script please.

thanks

|||

SELECT * INTO NewEmployee FROM Employee WHERE 1 = 0

above query will create same structure table called NewEmployee with structure of Employee. But will not have triggers and primary keys etc. you can create them by using follwing scripts

SELECT *

FROM sysobjects

WHERE parent_obj = OBJECT_ID('Employee')

SELECT *

FROM syscomments

WHERE id IN ( SELECT id

FROM sysobjects

WHERE parent_obj = OBJECT_ID('Employee') )

|||thanks for the reply Dinesh. Nice sql Stmt, does this work when you have a MS Access linked Server, is there a sysobjects table on the linked server?

Friday, February 17, 2012

Copy Table

How can I copy the table structure only (no data) keeping foreing key, indexes ...etc. I used DTS and it copied the table but not the relationship to other tables of index information
Thanks
Use Enterprise Manager to script the table. Just right click on the table
and select 'Generate SQL script', from the 'All tasks...' menu. Choose
appropriate options.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:0130896A-7E01-409A-B111-3788CFD58522@.microsoft.com...
> How can I copy the table structure only (no data) keeping foreing key,
indexes ...etc. I used DTS and it copied the table but not the relationship
to other tables of index information
> Thanks

Copy Table

How can I copy the table structure only (no data) keeping foreing key, indexes ...etc. I used DTS and it copied the table but not the relationship to other tables of index information
ThanksUse Enterprise Manager to script the table. Just right click on the table
and select 'Generate SQL script', from the 'All tasks...' menu. Choose
appropriate options.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:0130896A-7E01-409A-B111-3788CFD58522@.microsoft.com...
> How can I copy the table structure only (no data) keeping foreing key,
indexes ...etc. I used DTS and it copied the table but not the relationship
to other tables of index information
> Thanks

Copy Table

How can I copy the table structure only (no data) keeping foreing key, index
es ...etc. I used DTS and it copied the table but not the relationship to o
ther tables of index information
ThanksUse Enterprise Manager to script the table. Just right click on the table
and select 'Generate SQL script', from the 'All tasks...' menu. Choose
appropriate options.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:0130896A-7E01-409A-B111-3788CFD58522@.microsoft.com...
> How can I copy the table structure only (no data) keeping foreing key,
indexes ...etc. I used DTS and it copied the table but not the relationship
to other tables of index information
> Thanks

Monday, March 19, 2012

Copying Indexes from one table to another table programatically

Copying Indexes from one table to another table programatically

Copying Indexes from one table to another table programatically

copying freextex indexes into multiple destination dbs on same dest server

Thursday, March 8, 2012

Copying data and indexes

Copying data and indexes

Copying data and indexes

Saturday, February 25, 2012

copyind data and indexes

Friday, February 24, 2012

Copy/move database with full-text index

Sunday, February 19, 2012

Copy Tables in SQL 2k5 - Need Help

Copy table Structure including primary keys, index etc.

Friday, February 17, 2012

Copy Table

Copy Table

Copy Table

sql server db

Blog Archive

About Me