How to query all of the tab_name in the same database

nibabug

when my program run, i want to creat a new table, but i do not know if the name is exist. so, i want to check that if the name can be used or no, but i don't know how to query all the table names give me a help, please i am just starting lerning SQL and VC++ thanks a lot

wuhuaiji

Krish KP

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'table_name') CREATE TABLE table_name ( .... "sysobjects" is the table which contains all objects created

Regards KP

Virendrak

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Student') SELECT 'Student exists.' ELSE SELECT 'Student does not exist.' -- or SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Student' -- or SELECT * FROM sysobjects WHERE name='Student' --OR IF OBJECT_ID('Student') IS NOT NULL PRINT 'Student exists.' ELSE PRINT 'Student does not exist.'

Never Think That You Have Failed Instead Always Think That u hav Better Chance Next Time...

GuyThiebaut

Hi, I created a function to check if a table exists, as I need to check for this sort of thing frequently. Here is the function code:

CREATE function dbo.DoesObjectExist(@Name varchar(200), @Type char(1))
returns varchar(7)

begin
declare @Result varchar(7)

if exists (select * from sysobjects
where name = @Name
and xtype = @Type)
	select @Result = 'present'
else	
	select @Result = ''

return @Result
end

This is how you check if the OffVansQtyOrdered table exists using this function:

if dbo.DoesObjectExist('OffVansQtyOrdered','u') = 'present'

I hope this is of help...

You always pass failure on the way to success.

nibabug

firstly thanks again, by the way,i am so sorry, that i do not really understand what you write above, and i do not know in VC++ , how to create a table, just like the question i write "a problem about m_pConnection->Execute", have you a good way to resolve it, or can can you tell me which problem exist in my program

wuhuaiji

GuyThiebaut

Hi, The first example is what is called a "user defined function". This is a means for SQL to process data based on parameters passed to the function and then return a value(or a table). In this case the function takes an object and object type as input. The object type, in the bottom piece of code, is "u" which means that the function only looks for tables. The advantage of a function is that you can create this code once and reuse it many times by just calling it with dbo.DoesObjectExist('tablename','u'). In terms of creating a SQL table in C++ I'm sorry to say that I cannot help you with that yet as I am learning C# at the moment. What I can recommend is google and searches with strings such as "create SQL table with C++" and "SQL create table". As a tool google is invaluable and has proved to be just this in my work - as there is always going to be an example somewhere out there of what you are trying to do. Good luck.