Post reply

Procedure with variables Null should be all

  • January 13, 2010 at 9:33 am

    #140709

    Hey all,

    Here is some code which I got from a news group. Hari N Sharma posted or is given credit for it.

    Thanks Hari. Anyways I changed it a bit to meet my needs, and that code is posted below:

    So I want to make a procedure, and want to be able to say what schema I want (ie DF) or leave it null or someother code and then it will return all the rows not just my schema.

    I don't seem to get it work. Have tried several combinations from other bits of logic I have found on the web but no go.

    Can someone see the issue?

    Thanks in advance.

    Doug

    Declare @var Varchar(10)

    SET @Var = 'DF'

    SELECT

    LEFT(USER_NAME(o.uid),10) AS Schema_Owner

    , LEFT(OBJECT_NAME(i.id),50) AS TableName

    , i.rowcnt Row_Count

    , CONVERT(numeric(15,2),(((CONVERT(numeric(15,2), i.reserved)) * 8192 / 1024.0))) AS Reserved_Size_KB

    , CONVERT(numeric(15,2),(((CONVERT(numeric(15,2), i.used)) * 8192 / 1024.0))) AS Used_Size_KB

    , i.dpages

    , o.refdate

    FROM sysindexes i

    INNER JOIN sysobjects o (NOLOCK)

    ON i.id = o.id

    WHERE (o.uid like CASE

    WHEN @Var is not null THEN @VAR

    ELSE '%' End)

    AND indid IN (0, 1, 255)

    and o.type = 'U'

  • January 13, 2010 at 11:14 am

    #1103212

    This seems to work:

    Declare @var Varchar(10)

    SET @Var = null

    SELECT

    LEFT(USER_NAME(o.uid),10) AS Schema_Owner

    , LEFT(OBJECT_NAME(i.id),50) AS TableName

    , i.rowcnt Row_Count

    , CONVERT(numeric(15,2),(((CONVERT(numeric(15,2), i.reserved)) * 8192 / 1024.0))) AS Reserved_Size_KB

    , CONVERT(numeric(15,2),(((CONVERT(numeric(15,2), i.used)) * 8192 / 1024.0))) AS Used_Size_KB

    , i.dpages

    , o.refdate

    FROM sysindexes i

    INNER JOIN sysobjects o (NOLOCK)

    ON i.id = o.id

    WHERE o.uid LIKE COALESCE(@var,'%')

    AND indid IN (0, 1, 255)

    and o.type = 'U'

    Although it is not advisable to query against the systables. I would research the alternatives (information_schema, sys. etc)

    hope this helps

    SQL guy and Houston Magician

  • January 13, 2010 at 1:16 pm

    #1103272

    Thanks for the help.

    🙂

  • January 13, 2010 at 2:28 pm

    #1103331

    The LIKE is not necessary.

    WHERE o.uid = ISNULL(@Var, o.uid)

    AND indid IN (0, 1, 255)

    AND o.type = 'U'

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply