November 23, 2007 at 4:16 am
Hi guys,
just a simple question:
How to Extract SP Column Names & Parameter(s) and their Datatypes,
just like :
"SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "
Hope there are ways to do this.
Many Thankz,
- Nianz -
November 23, 2007 at 4:27 am
Have a look at the sys.parameters system view (on 2005, on 2000 look at dbo.syscolumns)
Regards,
Andras
November 23, 2007 at 4:30 am
SELECTo.name, o.type_desc, p.name, t.name, p.max_length, p.precision, p.scale,
p.is_output, p.is_cursor_ref, p.has_default_value, p.is_xml_document, p.default_value
FROMsys.all_objects o
inner join sys.all_parameters p on o.object_id = p.object_id
inner join sys.types t on p.user_type_id = t.user_type_id
order by o.type_desc, o.name, p.name
--Ramesh
November 25, 2007 at 10:00 pm
ramesh or any one,
can you convert to sql 2000? =)
SELECTo.name, o.type_desc, p.name, t.name, p.max_length, p.precision, p.scale, p.is_output, p.is_cursor_ref, p.has_default_value, p.is_xml_document, p.default_value FROM sys.all_objects oinner join sys.all_parameters p on o.object_id = p.object_idinner join sys.types t on p.user_type_id = t.user_type_idorder by o.type_desc, o.name, p.name
November 25, 2007 at 10:30 pm
I tried using:
select * from
INFORMATION_SCHEMA.PARAMETERS
but its only return the SP Parameters. but no SP Column(s) Name.
What i want is to List all the Column Name of a specific SP. =)
November 25, 2007 at 11:26 pm
What exactly do you mean by sp column outputs?
--Ramesh
November 25, 2007 at 11:36 pm
If i have this SP:
--
CREATE PROC usp_SimpleSP
AS
BEGIN
SELECT
[EmployeeID] = 13,
[Name] = 'Some name',
[Age] = 20,
[Deleted] = 0
END
--
What I want is, a script that will return its column name such :
"EmployeeID", "Name", "Age" and "Deleted", and if possible its corresponding datatypes.
"What exactly do you mean by sp column outputs?
--Ramesh "
November 26, 2007 at 12:13 am
Nianz,
This information is not available in any versions of the SQL. I wish this could be added as an enhancement in the next release, may be in SQL 2K8.;)
--Ramesh
November 26, 2007 at 12:26 am
I wish there are existing workaround. =(
November 26, 2007 at 1:58 am
Me too, still looking for such work-around.....:cool: And couldn't able be find till now!!!;)
--Ramesh
November 26, 2007 at 1:59 am
Nianz (11/26/2007)
I wish there are existing workaround. =(
It is actually not such a simple thing. For example the following returns a table with an integer column on Mondays, and a table with a varchar column on other days.
CREATE PROC Monday
AS
IF DATEPART(dw, GETDATE()) = 2
SELECT 1 AS someint
ELSE
SELECT 'Today is not Monday' AS somechars
GO
One workaround is to write a parser for TSQL, and parse the SQL statements. While it will not help in cases where the returned table is different depending on certain conditions, it can help in "more normal" cases. Note, that (having done something like this) this is not an afternoon project.
Regards,
Andras
November 26, 2007 at 5:43 am
Hi !,
SELECTPro.[name] AS SPName, Para.[name] AS ParameterName,
Typ.[name] AS ParameterDataType, Pro.[type_desc]
FROMsys.procedures Pro
INNER JOINsys.parameters Para
ONPro.object_id = Para.object_id
INNER JOINsys.Types Typ
ONPara.system_type_id = typ.system_type_id
ORDER BY Pro.[name]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply