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.

comprehensive details for setting up SSE?

First, soapbox questions for someone who might have more direct pull with Microsoft (or at least knowledge of how I should work with what they've given us), then a more specific question:

Scenario: You install SQL Server Express (or any other version, it doesn't matter) and the feedback with all the little green checkmarks tells you it has installed successfully. "Oh goody," say people (like me) who are not super experienced with SQL Server, "it installed successfully; now I can jump in and start using it!" But it aint so--there are so many other hoops to jump through before it really becomes operational.

After successful installation, why can't some link become visible indicating what you have to do after that? I mean, it's fine to wade through all the questions in this forum and get answers from all you nice and informed guys, AFTER the problems start coming; but I would rather not have to associate with you, to be honest, in terms of time spent that could be going into my projects.

Why isn't there a comprehensive guide right off the bat? That is, something referenced immediately that tells you such things as: how to register a database with emphasis on the fact that just creating a database won't do the trick for your application; how to set permissions and rights; that (what was I reading in the advice of one post?) you have to register both the database and the user, blah blah; all about instances, the web.config file, etc. etc. I mean, doesn't it seem logical that by virtue of a person installing the database, come on, that this is a pretty good indication that he/she in all likelihood is new at it? As it is now, all they tell you on the SSE site is that you have to have the .NET Framework installed; and the green checkmarks indicating that you've installed it correctly (sorry for mentioning that twice).

"OK, self," I say, "I have the Framework installed and SSE installed correctly, so let's get to work." Then the trouble begins. Unless Microsoft's purpose is to keep you tech guys and book writers in business, I don't understand why they don't give more up-front guidance. Any thoughts on why they work it this way? Bueller, Bueller? Anyone, anyone?

Now, to the question that's pressing me at the moment: I've installed SSE and I'm going through a tutorial ("How Do I: Create Data-Driven Web Sites?" onhttp://www.asp.net/getstarted/default.aspx?tabid=61). Yeah, the guy makes it look really easy. He creates a database then shuts down the connection. I try to do the same things and I'm informed that I don't even have a valid connection! How SQL-Server-Express fun and easy is that? It's a real AdventureWorks, if you ask me.

OK, that's my rant; and since MS didn't think in advance and implement my idea of a link to jump off from the installation menu, I have to face the reality of learning this by putting all the pieces together myself. So, my question is, before I try to go through the video and get more frustrated, WHERE DO I GO FOR THE MOST BASIC OF ALL THE STEPS I NEED TO DO, ONE BY ONE? This is like the second "beginner" video I've tried to go through but even these don't start at the very beginning with information on how to register a new database, making the user a part of aspnetWHATEVER, what permissions to dole out, etc.

Guidance is solicited and will be most appreciated. Thanks, Bryan

Hi,

I saw this: Http:www.sswug.org/see/21350 I haven't done the registration to access this site, so I can't say it will help. (Just came across it in a search). If you do check it out, let me know if it seems worthwhile.

BTW, I could find no Microsoft webcast/vids on installing SSE. Being a free product, the support is very limited. It's also a very complex and powerful program, and maybe it should be more surprising that the install is easy for anyone! I never seem to have the 'Standard' or 'Typical' system configuration that would make for an easy install. As i recall, the install of SQL Server 2005 (Standard) went easier for me than the SSE install.

I did install SSE for use with developing ASP.Net apps - works better with the defaults, and doesn't pose risks to the full version install. BRN..