SQL Server Performance

Query to find out wrong datatype and size of PROCEDURE in DataBase

Discussion in 'SQL Server 2008 General DBA Questions' started by baburk, Oct 4, 2009.

  1. baburk New Member

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

    Lots of Procedures 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
  2. FrankKalis Moderator

    You can try to match them by comparing the outcome of INFORMATION_SCHEMA.COLUMNS against the outcome of INFORMATION_SCHEMA.PARAMETERS.
  3. Madhivanan Moderator

    Do you want to findout the procedures whose parameters have different length or you want to alter the procedure to have same length as that of table?
  4. baburk New Member

    [quote user="Madhivanan"]
    Do you want to findout the procedures whose parameters have different length or you want to alter the procedure to have same length as that of table?
    [/quote]
    I will be happy if I get both?.
    Thanks for the reply.
  5. Madhivanan Moderator

    Have you tried anything with the suggestion from Frank?
  6. baburk New Member

    [quote user="Madhivanan"]
    Have you tried anything with the suggestion from Frank?
    [/quote]
    I don't know how to do with
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
  7. FrankKalis Moderator

    [quote user="baburk"]
    [quote user="Madhivanan"]
    Have you tried anything with the suggestion from Frank?
    [/quote]
    I don't know how to do with
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
    [/quote]
    Come on... [;)]
    Both views show you the type used. If your parameters are named after the columns to which they refer, you can compare them manually side by side.

Share This Page