How can I get a table reference knowing his name inside a system f

4 Nov 2005 3:46 PM

Argiris Petromelidis

I want to call the system function
DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
parameter a @TableName varchar variable represanting the table's name.

DBCC CHECKIDENT( ) requires as input the table object, while I have the
table string name.

Is there any way that I can get the table object using its string name
inside the DBCC CHECKIDENT() function ?

example
Create procedure procName (@TableName varchar)
begin
..........
DBCC CHECKIDENT( theTableObject , RESEED, 100)
........
end;

4 Nov 2005 4:26 PM

Russell Fields

Aigiris, Try using the OBJECT_ID function. From the BOL below. - RLF

OBJECT_ID
Returns the database object identification number.

Syntax
OBJECT_ID ( 'object' )

Show quote

"Argiris Petromelidis" <Argiris Petromeli***@discussions.microsoft.com>
wrote in message news:AC4CE899-E310-47C3-9101-B12779C2FDA8@microsoft.com...
>I want to call the system function
> DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
> parameter a @TableName varchar variable represanting the table's name.
>
> DBCC CHECKIDENT( ) requires as input the table object, while I have the
> table string name.
>
> Is there any way that I can get the table object using its string name
> inside the DBCC CHECKIDENT() function ?
>
> example
> Create procedure procName (@TableName varchar)
> begin
> ..........
> DBCC CHECKIDENT( theTableObject , RESEED, 100)
> ........
> end;

5 Nov 2005 10:39 AM

John Bell

Hi

You may want to try using dynamic SQL e.g.

CREATE PROCEDURE MyCheck ( @objectname sysname )
AS
    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@objectname)
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    AND EXISTS ( SELECT * FROM dbo.syscolumns WHERE id = OBJECT_ID(@objectname)
AND COLUMNPROPERTY(id,name,'IsIdentity') = 1)
    BEGIN
        DECLARE @cmd varchar(8000)
        SET @cmd = 'DBCC CHECKIDENT( ''' + QUOTENAME(@objectname) + ''', RESEED,
100)'
        EXEC (@cmd)
    END

John

Show quote

"Argiris Petromelidis" wrote:

> I want to call the system function
> DBCC CHECKIDENT( ) inside a stored procedure which takes as an input
> parameter a @TableName varchar variable represanting the table's name.
>
> DBCC CHECKIDENT( ) requires as input the table object, while I have the
> table string name.
>
> Is there any way that I can get the table object using its string name
> inside the DBCC CHECKIDENT() function ?
>
> example
> Create procedure procName (@TableName varchar)
> begin
>   ..........
>   DBCC CHECKIDENT( theTableObject , RESEED, 100)
>   ........
> end;