disable sp_helpindes "no index" message" -- sql server programming

Jay
10/8/2007 6:35:37 AM

The OUTPUT parameter I was thinking of is the one to sp_executesql.

I'm working in T-SQL and will sometimes push the output to the query window
(which is what I'm doing now), though thinking about it, that will be
uncommon in the long run.

I'll probably try supressing the message via sp_executesql, but as I said,
I'm still learning this stuff and have found the details to capturing output
from dynamic sql to be confusing.

[quoted text, click to view]

Dan Guzman
10/8/2007 6:52:04 AM

[quoted text, click to view]

RAISERROR with severity less than 11 is an information message, not an error
message. An informational message does not raise an exception when returned
in ADO or ADO.NET. The application code can either ignore or process the
message as desired. The details depend on the API you are using.

[quoted text, click to view]

I'm not sure I understand what you mean about the OUTPUT parameter here.
The only parameter to sp_helpindex is the @objname INPUT parameter.
sp_helpindex will return a non-zero return code on failure, but no indexes
on the specified object is not a failure so the return code is zero. You
can check for an empty resultset in your code to determine if the objects
has no indexes.

If you need help processing parameters and return codes, let us know what
language and API you are using.

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Uri Dimant
10/8/2007 7:15:02 AM

Jay
Take a look at Kalen's script

CREATE VIEW get_index_columns
AS
SELECT object_name(ic.object_id) as object_name , index_name = i.name,
'column' = c.name,
'column usage' = CASE ic.is_included_column
WHEN 0 then 'KEY'
ELSE 'INCLUDED'
END
FROM sys.index_columns ic JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
JOIN sys.indexes i
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id

After creating the view, you can select from it, and it will give you the
KEY columns and the INCLUDED columns in all the indexes in all the tables.
Or, you can add a WHERE clause for your own table or index:

SELECT * FROM get_index_columns
WHERE object_name = 'mytable'

[quoted text, click to view]

Dan Guzman
10/8/2007 8:33:16 PM

You'll have more control processing resultsets and messages in application
code. I don't think you can do much with informational messages on the back
end; those are always returned to the client.

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Jay
10/8/2007 8:40:44 PM

I will do what I can with T-SQL, unless there is something else that comes
with the engine for free and is from Microsoft, like Windows Power Shell,
which I will get into at a later time.

Thanks,
Jay

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : disable sp_helpindes "no index" message"