Need some knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen PerssonSteen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...

Need some knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen PerssonSteen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> I need to get my knowledge about indexes expanded a little bit...
>> What's the best/easiest way to view Indexes and their definition in
>> SQL server. I can look in sysindexes, but as I see it it only shows
>> me the different indexes by name, but not so much about the
>> definition etc. (or maybe I just don't know what to look for). I can
>> also use sp_helpindex, but that only shows me very little as well. I
>> know I can look up the difinition in EM, but that's a bit cumbersome
>> to click through all tables to see it. Reason for asking is that I
>> have 2 almost identical databases, where on one of them there's a
>> number of indexes defined. These Indexes I'd like to create in the
>> second database as well.
>> Best Regards
>> Steen Persson

Need some knowledge about indexes

Hi
I need to get my knowledge about indexes expanded a little bit...
What's the best/easiest way to view Indexes and their definition in SQL
server. I can look in sysindexes, but as I see it it only shows me the
different indexes by name, but not so much about the definition etc. (or
maybe I just don't know what to look for). I can also use sp_helpindex, but
that only shows me very little as well. I know I can look up the difinition
in EM, but that's a bit cumbersome to click through all tables to see it.
Reason for asking is that I have 2 almost identical databases, where on one
of them there's a number of indexes defined. These Indexes I'd like to
create in the second database as well.
Best Regards
Steen Persson
Steen
Run this script in QA on source database .
SELECT s1.name, s2.name,
INDEX_COL( s1.name, s2.indid, 1 ),
CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
WHEN 1 THEN 'Clustered'
ELSE 'Non-clustered'
END
FROM sysobjects s1
INNER JOIN sysindexes s2
ON s1.id = s2.id
WHERE s1.xtype = 'U'
AND s2.indid > 0 AND s2.indid < 255
AND s2.name not like '_WA_Sys%'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>
|||Hi,
You could join the below system tables to get all the details of Indexes:-
sysindexes (name and indid columns)
sysindexkeys (object_id,colid)
syscolumns (colid,name)
Note:-
Querying the system tables might not be a good option... But for your query
I could see only this solution
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...
> Hi
> I need to get my knowledge about indexes expanded a little bit...
> What's the best/easiest way to view Indexes and their definition in SQL
> server. I can look in sysindexes, but as I see it it only shows me the
> different indexes by name, but not so much about the definition etc. (or
> maybe I just don't know what to look for). I can also use sp_helpindex,
but
> that only shows me very little as well. I know I can look up the
difinition
> in EM, but that's a bit cumbersome to click through all tables to see it.
> Reason for asking is that I have 2 almost identical databases, where on
one
> of them there's a number of indexes defined. These Indexes I'd like to
> create in the second database as well.
> Best Regards
> Steen Persson
>
|||Great...that helps a lot...
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> Run this script in QA on source database .
> SELECT s1.name, s2.name,
> INDEX_COL( s1.name, s2.indid, 1 ),
> CASE INDEXPROPERTY( s1.id, s2.name, 'IsClustered' )
> WHEN 1 THEN 'Clustered'
> ELSE 'Non-clustered'
> END
> FROM sysobjects s1
> INNER JOIN sysindexes s2
> ON s1.id = s2.id
> WHERE s1.xtype = 'U'
> AND s2.indid > 0 AND s2.indid < 255
> AND s2.name not like '_WA_Sys%'
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uqbvCcTeEHA.720@.TK2MSFTNGP11.phx.gbl...

Need knowledge about patch

Hello, everyone:
Are ther any basic information about patch on SQL Server? How to develop patch, and run it? Thanks a lot
ZYTI don't understand exactly what you are asking. Do you want to know about the application of SQL Service Packs?

It sounds to me like you are trying to figure out how to create a patch for SQL Server, which is something that Microsoft frowns on because it makes the patched server practically impossible for them to support.

-PatP|||Pat:

Thanks for reply. I don't need MS SQL Server Pack. What I need is a .exe file that can modify database and tables. As you said, I want to know how to create a patch.

ZYT|||Can you describe in a little more detail what you want to do? I'm still confused trying to figure out what you want, so I'm no help explaining how you could do it!

-PatP|||Pat:

Tell you truth, I don't have experience on SQL Server patch. I was asked to develop a .exe file that can insert, delete and trunc tables. This file will be put in network disk. Someone copy it to local machine and run it to modify database. Thanks.

ZYT|||Sounds like he's looking for a mini Enterprise Manager. Also sounds dangerous.|||Dangerous would be a HUGE understatement! There are ways that this could be done that weren't as bad as others, but this just sounds like a disaster in the making to me.

The fundamental process of creating a list of tables, and allowing the user to truncate or drop them isn't complex. I'd guess that a rather smallish VB program could do that. The problem is that it is very dangerous!

Creating tables would be a very slightly more complex process. Nothing earth-shattering, just a bit more complex than manipulating the existing tables. This is also a relatively safe operation compared to the other two.

I'd have a long, heart-to-heart talk with whoever wants you to build this little beastie. I'd treat this project kind of like building a gallows, since it is pretty sure to hang the dba once it gets put to use!

-PatP|||Agreed! I have little VB apps I wrote to do this kind of thing when I don't want to wait for EM to load but since it's MY neck on the line I am VERY careful who has access to these things. Me. Only Me. Nothing but ME. So help Me Me.