need cursor help

hi
i want to create the sp to recreate the indexes of all the table. here
is what i need:
CREATE UNIQUE CLUSTERED INDEX
[table_name$32$0_idx] ON
[dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
WITH DROP_EXISTING
in order to get all the <table name> (columns)
i use sp_pkeys:
exec sp_pkeys [table_name$32$0]
here is the table where i insertet sp_pkeys data.
create table keys (
table_qualifier sysname,
table_owner sysname,
table_name sysname,
column_name sysname,
key_seq smallint,
pk_name sysname
)
i think i need to create a temp table and insert the data into it and
then fetch all i need into cursor ant exec CREATE INDEX WITH
DROP_EXISTING statment, but
i don't know how to convert column into a row (as sp_pkeys returns all
the index's keys into 1 column) .
thanxHi,
You could use DBCC DBREINDEX command to Reindex all indexes.
Use the below script to reindex all tables in a database:-
EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
Thanks
HARI
SQL Server MVP
"benamis" <nera@.meilo.lt> wrote in message
news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
> hi
> i want to create the sp to recreate the indexes of all the table. here is
> what i need:
> CREATE UNIQUE CLUSTERED INDEX
> [table_name$32$0_idx] ON
> [dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
> WITH DROP_EXISTING
> in order to get all the <table name> (columns)
> i use sp_pkeys:
> exec sp_pkeys [table_name$32$0]
> here is the table where i insertet sp_pkeys data.
> create table keys (
> table_qualifier sysname,
> table_owner sysname,
> table_name sysname,
> column_name sysname,
> key_seq smallint,
> pk_name sysname
> )
> i think i need to create a temp table and insert the data into it and
> then fetch all i need into cursor ant exec CREATE INDEX WITH DROP_EXISTING
> statment, but
> i don't know how to convert column into a row (as sp_pkeys returns all the
> index's keys into 1 column) .
> thanx|||Maybe this might help:
http://milambda.blogspot.com/2005/0...in-current.html
ML|||yes i know about DBCC DBREINDEX , but CREATE INDEX WITH DROP_EXISTING
is more efective [~2x times] (at least for navision db).
Hari Pra wrote:
> Hi,
> You could use DBCC DBREINDEX command to Reindex all indexes.
> Use the below script to reindex all tables in a database:-
> EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
> Thanks
> HARI
> SQL Server MVP
> "benamis" <nera@.meilo.lt> wrote in message
> news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
>
>|||If you want to rebuild indexes without using the DBCC command, you can scrip
t
your the indexes and maybe create a job using this script.
You can script any object easily in the Enterprise Manager.
ML

Need basic SQL query help

Trying to select unique records from a database. A combination of 2 variables make the record unique. Need to export all rows of the return. I have below rudimentary sql skills (select/where) and cannot figure this one out. The database has ~ 180k rows, of which there are only ~ 50k unique records.

The data is on a single table, 15 seperate columns. It is a table containing network router data. The existing table set up shows the historical progression of changes/updates to these routers. I am trying to determine the latest records of the routers in question. Since some of the IP addresses have been used multiple times (moved from site A to site B for example), there are multiple dupe records that I do not want.
The two fields I need to use to identify unique records are:
serial_IP and site_id
In addition to that, I would also like to know how to format "max date" query, so that I get the latest information. I can do that manually if I must via a sort when I export the data, but it would be nice to know how to do that in the future.

thanks

noclueAs those two columns make a unique key on the table, there is possibility of use of a DISTINCT keyword, such as

SELECT DISTINCT serial_IP, site_id FROM your_table;

However, regarding your next request (selecting records with the latest date), there's need to use a subquery:SELECT DISTINCT t.serial_IP, t.site_id
FROM your_table t
WHERE t.date_column = (SELECT max(t1.date_column)
FROM your_table t1
WHERE t1.serial_IP = t.serial_IP
AND t1.site_id = t.site_id
);The DISTINCT keyword might, or might not be needed in this case: if table has only one record per "date_column", you won't need that. If there are multiple records per every "date_column" value, you'll still need it to eliminate multiple records.