Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-10-06 : 07:41:05
|
| sachin writes "1) Given the name of an index how to find the columns associated with the particular index in SQL Server?2) Given the name of a constraint, how to find whether it is enabled or disabled?3) Is there any equivalent of the Oracle 'valid' state in SQL Server? e.g. In Oracle 'valid' state is defined for views, indexes. Are there equivalent concepts in SQL Server and if there are, how to find if a particular index/view is 'valid' or not?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-06 : 07:43:42
|
| 1. sp_helpindex 'myTable' --lists all indexes and their columns2. select objectproperty(id, 'CnstIsDisabled') from sysobjects where name='myConstraint'3. You'll have to clarify what "valid" means exactly. The OBJECTPROPERTY function has many options to provide state information. Books Online has all the details. |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-10-06 : 07:44:33
|
| 1) User INDEX_COL function2) sp_helpconstraint 'constraint name'3) What does VALID in ORACLE means? If you mean to say, changes to view makes it invalid, you can use sp_refreshview but there is no such thing as INVALID state for view/indexHarsh AthalyeIndia."Nothing is Impossible" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-10-06 : 10:23:31
|
Well. How was the interview?  MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-06 : 10:28:48
|
| SQL Server interviewer: "What datatype would you use for Age and Name columns?"Ex Oracle'r : "I'd use INT and VARCHAR2"Interviewer: "Too?"Oracle'r : "Yeah, 2"Hehehe!Kristen |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-10-06 : 10:34:53
|
Rob:1) Naughty. Use the views(information_schema), not the sys tables.2) see 13) Oracle is teh debbil!  [Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-06 : 11:37:31
|
The info_schema views don't contain index or constraint status info.  |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-10-10 : 14:07:23
|
wtf, they don't give you good information like that???? Hope they change that soon!I shall assume you agree with 1) and 3)  [Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
|
|
|
|
some tough SQL Server queries