Query to find out wrong datatype and size of sp

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query to find out wrong datatype and size of sp

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-10-05 : 01:07:55

We are having 125 tables and more than 1800 sp in our database with 8 schema.

Lots of sps are given with wrong data type and size.

Can anybody help me to find it out by sp and schema wise.

CREATE TABLE [UserDetails].[DTUser]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Address] [varchar](30) NULL,
[State] [nvarchar](80) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

CREATE PROCEDURE [UserDetails].[DTUserInsert]
-- Add the parameters for the stored procedure here
@Name nvarchar(30),
@Address nvarchar(30),
@State nvarchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT
INTO [UserDetails].[DTUserInsert]
(
[Name] ,
[Address] ,
[State]
)
VALUES
(
@Name ,
@Address ,
@State
)
END GO

Thanks,
Babu Kumarasamy

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-05 : 07:28:39

This is how you can get the data types of the parmaters in your sprocs. Your post is pretty vague, so you'll have to be more specific if you want a more precise answer

select object_name(par.object_id),[ProcedureName] = par.name
,st.[Name],st.schema_id,st.max_length,st.precision,st.scale
from sys.parameters par
inner join sys.types st on
par.system_type_id = par.system_type_id
and par.user_type_id = st.user_type_id

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-05 : 14:11:44

i think you need to start with sp_depends to get dependent routines of a table and then look for column names against params. otherwise, there can be chance of same field names existing on multiple table with different types and you cant correlate which one procedure is refering to

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-05 : 14:21:35

you could create a cursor from information.schema.routines, then insert into a table, the execution of sp_helptext...then you could do a search of all your sprocs

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

baburk
Posting Yak Master

108 Posts

Posted - 2009-10-06 : 00:15:00

quote:
Originally posted by jimf

This is how you can get the data types of the parmaters in your sprocs. Your post is pretty vague, so you'll have to be more specific if you want a more precise answer

select object_name(par.object_id),[ProcedureName] = par.name
,st.[Name],st.schema_id,st.max_length,st.precision,st.scale
from sys.parameters par
inner join sys.types st on
par.system_type_id = par.system_type_id
and par.user_type_id = st.user_type_id

Jim

Everyday I learn something that somebody else already knew

Hi you gave me a good reply.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS

How can I able to match the Table Columns and the Parameters.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources