July 12, 2010 at 9:15 am
Hello everyone,
I am trying to figure out what the properties are for a stored procedure that I have created. My company has it own version of sp_helptext. However, it hardcodes stuff like ANSI_NULL, QUOTED_IDENTIFIERS, etc. I am trying to make this pull from the system itself.
Can anyone help me on what system tables store this information?
Thanks,
Fraggle.
July 12, 2010 at 11:30 am
ANSI_NULL and the rest are connection settings. If you want procedure properties you can look at the system view, sys.procedures.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 13, 2010 at 10:20 am
Grant,
I believe I must be blind as I don't see it in that specific system view. Is it being called something else. Note that I am using 2005 Standard Ed.
Thanks,
Fraggle.
July 13, 2010 at 11:09 am
Couldn't connect to a 2005 server real quick, but I got this off of MSDN. It should be there in 2005.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2010 at 7:07 am
Grant,
Sorry, still not seeing it. Must be going blind or something. Sorry to keep pestering you about it.
Fraggle.
July 14, 2010 at 7:12 am
Are you sure you're in SQL Server 2005? I just connected up to one of our 2005 instances, ran it and got back information. It's right there in the DB. Maybe you're in 2000 compatibility mode or something?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2010 at 7:58 am
Fraggle-805517 (7/12/2010)
I am trying to figure out what the properties are for a stored procedure that I have created.
Use the OBJECTPROPERTYEX built-in function. Parameters of interest include ExecIsAnsiNullsOn and ExecIsQuotedIdentOn.
Just an alternative.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 14, 2010 at 9:50 am
Grant, yes I am sure I am in 2005. The only columns I see are as follows.
SELECT top 10 *
FROM sys.procedures
Returns the following:
Name
Object_id
Principal_id
Schema_id
Parent_Object_id
Type
Type_desc
create_date
modify_date
is_ms_shipped
is_published
is_schema_published
is_auto_executed
is_execution_replicated
is_repl_serializable_only
skips_repl_constraints
Nothing in that list for ANSI_NULLS or QUOTED_IDENTIFIERS
Thanks,
Fraggle
July 14, 2010 at 9:50 am
Paul,
That works perfect. Thank you very much. That will solve the problem.
Fraggle.
July 15, 2010 at 1:17 am
Fraggle-805517 (7/14/2010)
Paul,That works perfect. Thank you very much. That will solve the problem.
Fraggle.
Good. By the way, I think Grant was thinking of the system view sys.sql_modules which does give the information you are after as well.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 15, 2010 at 5:38 am
Paul White NZ (7/15/2010)
Fraggle-805517 (7/14/2010)
Paul,That works perfect. Thank you very much. That will solve the problem.
Fraggle.
Good. By the way, I think Grant was thinking of the system view sys.sql_modules which does give the information you are after as well.
Paul
Actually, no, I wasn't. I wish I had been. I was trying to answer a question not asked. Good catch. Thanks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 15, 2010 at 7:21 am
Grant Fritchey (7/15/2010)
I was trying to answer a question not asked.
Then you have saved 'Fraggle' posting a future question - yay!
:laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply