怎样判断表是否存在

在sqlserver中
select *from sysobjects where name="要查询的表名"
如果返回结果不为0,则该表存在.

如果是sql server:
select name   from sysobjects where uid = (select uid from sysusers where name='username')
access:
CTables.h:
class CTables : public CRecordset
{
virtual CString GetDefaultConnect() { return ""; }
virtual CString GetDefaultSQL() { return ""; }public:
CTables(CDatabase* pDatabase);
BOOL            Open(LPCSTR pszTableQualifier = NULL,
LPCSTR pszTableOwner = NULL,
LPCSTR pszTableName = NULL,
LPCSTR pszTableType = NULL,
UINT   nOpenType = forwardOnly); CString         m_strTableQualifier;
CString         m_strTableOwner;
CString         m_strTableName;
CString         m_strTableType;
CString         m_strRes; virtual void    DoFieldExchange(CFieldExchange*);
};
CTables.cpp:#include "CTables.h"//---------------------------------------------------------------------
// CColumnPrivileges
//---------------------------------------------------------------------
// CTablesCTables::CTables(CDatabase* pDatabase)
: CRecordset(pDatabase)
{
m_strTableQualifier = _T("");
m_strTableOwner     = _T("");
m_strTableName      = _T("");
m_strTableType      = _T("");
m_strRes        = _T("");
m_nFields = 5;
}BOOL CTables::Open(LPCSTR pszTableQualifier,
LPCSTR pszTableOwner,LPCSTR pszTableName,LPCSTR pszTableType,
UINT nOpenType)
{
RETCODE nRetCode;
UWORD   bFunctionExists; // Make sure SQLTables exists
AFX_SQL_SYNC(::SQLGetFunctions(m_pDatabase->m_hdbc,
SQL_API_SQLTABLES,&bFunctionExists));
if (!Check(nRetCode) || !bFunctionExists)
{
if (!bFunctionExists)
TRACE(_T("SQLTables not supported\n"));
return FALSE;
} // Cache state info and allocate hstmt
SetState(nOpenType,NULL,readOnly);
if (!AllocHstmt())
return FALSE; TRY
{
OnSetOptions(m_hstmt);
AllocStatusArrays(); // Call the ODBC function
AFX_ODBC_CALL(::SQLTables(m_hstmt,
(UCHAR FAR*)pszTableQualifier,SQL_NTS,
(UCHAR FAR*)pszTableOwner,SQL_NTS,
(UCHAR FAR*)pszTableName,SQL_NTS,
(UCHAR FAR*)pszTableType,SQL_NTS));
if (!Check(nRetCode))
ThrowDBException(nRetCode,m_hstmt); // Allocate memory and cache info
AllocAndCacheFieldInfo();
AllocRowset(); // Fetch the first row of data
MoveNext(); // If EOF, result set is empty, set BOF as well
m_bBOF = m_bEOF; } CATCH_ALL(e)
{
Close();
THROW_LAST();
}
END_CATCH_ALL return TRUE;
}void CTables::DoFieldExchange(CFieldExchange* pFX)
{
pFX->SetFieldType(CFieldExchange::outputColumn);
RFX_Text(pFX,_T("TABLE_QUALIFIER"),m_strTableQualifier);
RFX_Text(pFX,_T("TABLE_OWNER"),m_strTableOwner);
RFX_Text(pFX,_T("TABLE_NAME"),m_strTableName);
RFX_Text(pFX,_T("TABLE_TYPE"),m_strTableType);
RFX_Text(pFX,_T("REMARKS"),m_strRes);
}使用时：
CDatabase db;
db.Open("aa");
CTables tb(&db); char lpszType[64];
strcpy(lpszType, "'TABLE'");
tb.Open(NULL,NULL,NULL,lpszType,CRecordset::snapshot);
while (! tb.IsEOF()){
if(tb.m_strTableName == "youtablename") break;
tb.MoveNext();
}
tb.Close();
db.Close();

SELECT name
  FROM sysobjects
  WHERE  name = N'<table_name, sysname, test_table>'
  AND type = 'U'试试吧！
^_^

IF NOT EXISTS (select name from sysobjects
where name=@TableName and type ='U')
.......

调试易

怎样判断表是否存在

解决方案 »