Newbie - can I do this in a script

I would like my script to check for the existance of a table, and if found
make sure theres enuf dispace back it up (otherwise display a message and
halt), then I need to alter a table. If it doesn't exist I would like to
create it like normal.
I've been trolling thru the sql help files but can't find how to check for
existence of a table. Any pointers/articles someone can get me too?
JeffYou can check the existence of a table using the following syntax:
IF OBJECT_ID('database.object_owner.object') IS NOT NULL
BEGIN
PRINT 'object exists'
END
eg. Checking if the Northwind Orders table exists
IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
BEGIN
PRINT 'object exists'
END
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> I would like my script to check for the existance of a table, and if found
> make sure theres enuf dispace back it up (otherwise display a message and
> halt), then I need to alter a table. If it doesn't exist I would like to
> create it like normal.
> I've been trolling thru the sql help files but can't find how to check for
> existence of a table. Any pointers/articles someone can get me too?
> Jeff
>
>|||Thanks Peter - I guess you answered my followup question about "if"
statements. How would I format a user defined procedure or a function?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END|||Will this work on just the db also (i.e. northwind)?
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> You can check the existence of a table using the following syntax:
> IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END
> eg. Checking if the Northwind Orders table exists
> IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> BEGIN
> PRINT 'object exists'
> END|||Jeff
I am not sure what you are asking, can you please clarify your question.
Thanks
- Peter Ward
WARDY IT Solutions
"J. Clarke" wrote:
> Will this work on just the db also (i.e. northwind)?
> Jeff
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:BD0B1E97-FE1F-4A38-89A9-A53385DBFCDC@.microsoft.com...
> > You can check the existence of a table using the following syntax:
> >
> > IF OBJECT_ID('database.object_owner.object') IS NOT NULL
> > BEGIN
> > PRINT 'object exists'
> > END
> >
> > eg. Checking if the Northwind Orders table exists
> >
> > IF OBJECT_ID('northwind.dbo.orders') IS NOT NULL
> > BEGIN
> > PRINT 'object exists'
> > END
>
>|||Sorry Ward - the question is can I check for the existance of a DB (vs. the
table)?
I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
the QA didn't seem to like it. I guess it doesn't matter too much since
that table will always be there if the DB is...
Jeff
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
> Jeff
> I am not sure what you are asking, can you please clarify your question.
> Thanks
>
> - Peter Ward|||Use DB_ID() for that:
IF DB_ID('pubs') IS NOT NULL
PRINT 'Database exists'
ELSE
PRINT 'Database doesn''t exists'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"J. Clarke" <jclarke@.docstorsysNOSPAM.com> wrote in message
news:O7KMBXHMFHA.508@.TK2MSFTNGP12.phx.gbl...
> Sorry Ward - the question is can I check for the existance of a DB (vs. the
> table)?
> I think I tried something like: IF OBJECT_ID('database') IS NOT NULL and
> the QA didn't seem to like it. I guess it doesn't matter too much since
> that table will always be there if the DB is...
> Jeff
>
> "P. Ward" <peter@.remove_online.wardyit.com> wrote in message
> news:F576737F-8C16-4D87-B830-7D3A95960907@.microsoft.com...
>> Jeff
>> I am not sure what you are asking, can you please clarify your question.
>> Thanks
>>
>> - Peter Ward
>