Comprehensive Index Information

Hi,

I am writing an in house utility to attempt to compare different
aspects of databases.
I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).

I would like the following information, in one result set if possible:

Table Name
Index Name
Column Name
Column Position
Unique?

Now on Oracle, this is easily done with the following query:

SELECT IND.TABLE_NAME, IND.INDEX_NAME, IND.COLUMN_NAME,
IND.COLUMN_POSITION, COL.UNIQUENESS
FROM USER_IND_COLUMNS IND,
USER_INDEXES COL
WHEREIND.INDEX_NAME = COL.INDEX_NAME
ORDER BY 1, 2, 3, 4, 5

I have been trying for over an hour now to get the equivalent, and I
really cannot figure it out. If anybody can come up with this then I
would greatly appreciate it!

Many Thanks,

PaulPaul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

I am writing an in house utility to attempt to compare different
aspects of databases.

Before you go too far, pay a visit to http://www.red-gate.com and
if SQL Compare meets your needs.

Quote:

Originally Posted by

I am currently writing the queries to list all of the indexes in the
database (including primary key indexes at present - I may move these
and compare separately at some point).
>
I would like the following information, in one result set if possible:
>
Table Name
Index Name
Column Name
Column Position
Unique?

SELECT tablename = t.name, indexname = i.name,
colname = c.name, pos = ic.index_column_id,
indextype = i.type_desc, isunique = i.is_unique
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.columns c ON t.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE i.is_hypothetical = 0

There are probably more columns should include in the output, but I
levae that as an exercise.

Note: the above works in SQL 2005 only. Next time, please specify which
version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Thanks for the link - unfortunately this is more of an exercise for
the time being and so we are not willing to spend money on a tool at
present!

Thanks for the help - if you do know something that will work on both
versions that would be good.

Paul|||Paul (paulwragg2323@.hotmail.com) writes:

Quote:

Originally Posted by

Thankyou very much for this. Of course, as usual I stupidly forgot to
post the version. Sorry about that. Really I need something that will
work on both SQL Server 2000 and SQL Server 2005.

Then you need to work against sysobjects, sysindexes, sysindexkeys and
syscolumns. The query will be similar, but you need to filter for
statistics, since in SQL 2000 statistics and indexes live in sysindexes.

These are documented in Books Online, and since this is an exercise for you,
I leave you there. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland.

Completely Script SQL 2005 Database

Hi Everyone, what im looking for is a way or a freeware utility that will genereate a single SQL Script, for my entire db...

Just like phpMyAdmin does for MySQL...

it would need to script my tables, the data of those tables, and all my stored proc's.

Can SQL Server management studio do this? or does anyone know of a utility that will.

Thanks, Justinhave you tried the Generate Scripts Wizard in SSMS?

You can get to it off any database node in Object Explorer, under the Tasks flyout menu. Look for "Generate Scripts..."

I don't think it can script the data though. For that you might take a look at this addin to SSMS:

http://www.codeproject.com/useritems/enisey.asp|||If you know a little C# and want to try writing your own, SMO exposes pretty much every object in SQL Server, and each one has a Script() method which returns a StringCollection.

So it would be a pretty simple matter to write your own app to do this using SMO. I have done it actually. When I get a chance I'll post it on my site - link below. I'll try to get it posted this weekend.|||Just saw this post, looks like it may be of use to you:

http://www.dbforums.com/showthread.php?t=1610233|||Here's the app I was talking about above. It doesn't script all the data, just the objects. It generates a separate file for each object. I did it that way so it's easy to put a db under source control if it's not already.

http://www.elsasoft.org/tools.htm

Edit: I couldn't help myself. So I just added some code so that scriptdb.exe that will script out the data as well (optionally) using bcp.exe. :)|||Hi,

Thank you for that script, it worked a charm,

how ever i was looking for something that will script it to a .sql file... as in, INSERT INTO type stuff...

Cheers, Justin