<div>
try like this 
IF NOT EXISTS 
(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tempdb..##TempData11' ) ) 
create table ##TempData11(...)
</div>

<div>
Can you please post the code ? 
 
Cheers, 
 
Aash.
</div>

<div>
Server: Msg 208, Level 16, State 1, Line 1 
Invalid object name 'sys.objects'. 
&nbsp;i am getting this error, i am using the code 
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '##TempData11') 
&nbsp; &nbsp;DROP TABLE &nbsp;##TempData11 
 
&nbsp;CREATE TABLE ##TempData11 
&nbsp; ( SG varchar(50),Number INT ) 
 
&nbsp; INSERT INTO &nbsp;##TempData11 
 
SELECT 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CASE &nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN servicename LIKE '%PLUS%' THEN 'SCSCXEPLUS' 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN servicename LIKE 'SSE%' THEN 'SSE' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN servicename LIKE 'SE%' THEN 'SE' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --WHEN servicename LIKE '%S0BB%' THEN 'SOBB' 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --WHEN servicename &nbsp;substring(ServiceName,0,len(ServiceName)-4) THEN 'SCX' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AS ServiceNameGroup,count(*) AS Number 
&nbsp; &nbsp; &nbsp; &nbsp; 
FROM 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;tbl_WeeklyLimit 
WHERE 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;servicename LIKE '%PLUS%' 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OR servicename LIKE 'SSE%' 
&nbsp; &nbsp; &nbsp; &nbsp; OR servicename LIKE 'SE%' 
&nbsp; &nbsp; &nbsp; &nbsp; --OR servicename LIKE '%S0BB%' 
&nbsp; &nbsp; &nbsp; &nbsp; --OR servicename &nbsp;substring(ServiceName,0,len(ServiceName)-4) 
&nbsp; &nbsp; &nbsp; &nbsp; 
GROUP BY 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CASE &nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN servicename LIKE '%PLUS%' THEN 'SCSCXEPLUS' 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN servicename LIKE 'SSE%' THEN 'SSE' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN servicename LIKE 'SE%' THEN 'SE' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --WHEN servicename LIKE '%S0BB%' THEN 'S0BB' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --WHEN servicename &nbsp;substring(ServiceName,0,len(ServiceName)-4)='SCX' THEN 'SCX' &nbsp; &nbsp; &nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;END &nbsp; 
 
--SELECT * FROM ##TempData11 
 
&nbsp; &nbsp; &nbsp; &nbsp; DECLARE Defaultval CURSOR FOR 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT Number FROM &nbsp;##TempData11 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OPEN Defaultval 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FETCH NEXT FROM Defaultval INTO @Number 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;--Print @Number 
 
&nbsp; 
--SELECT &nbsp;COUNT(ServiceName) FROM tbl_WeeklyLimit Where substring(ServiceName,0,len(ServiceName)-4)='SCX' 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET @SQL = 'Create Table #TableName (' &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; While (@Number&gt;0) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET &nbsp;@SQL=@SQL + 'column'+ CAST(@Number as varchar)+ ' varchar(50) ,' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET @Number = @Number - 1 
&nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET @SQL=Substring(@SQL,0,len(@SQL)-1) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET @SQL=@SQL +')' 
&nbsp; &nbsp; 
&nbsp; 
 
&nbsp; print @SQL 
&nbsp; EXEC (@SQL) 
 
&nbsp; CLOSE Defaultval 
&nbsp; DEALLOCATE Defaultval 
so when i am executing the query i am getting the message like 
Server: Msg 2714, Level 16, State 6, Line 13 
There is already an object named '##TempData11' in the database. 

</div>

<div>
i am using the below code 
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '##TempData11') 
&nbsp; &nbsp;DROP TABLE &nbsp;##TempData11 
 
&nbsp;CREATE TABLE ##TempData11 
&nbsp; ( SG varchar(50),Number INT ) 
 
&nbsp; INSERT INTO &nbsp;##TempData11 
 
SELECT 
&nbsp; &nbsp; &nbsp; CASE &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN servicename LIKE '%PLUS%' THEN 'SCSCXEPLUS' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN servicename LIKE 'SSE%' THEN 'SSE' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN servicename LIKE 'SE%' THEN 'SE' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --WHEN servicename LIKE '%S0BB%' THEN 'SOBB' 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --WHEN servicename &nbsp;substring(ServiceName,0,len(ServiceName)-4) THEN 'SCX' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; END 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS ServiceNameGroup,count(*) AS Number 
&nbsp; &nbsp; &nbsp; &nbsp; 
FROM 
&nbsp; &nbsp; &nbsp; tbl_WeeklyLimit 
WHERE 
&nbsp; &nbsp; &nbsp; servicename LIKE '%PLUS%' 
&nbsp; &nbsp; &nbsp; OR servicename LIKE 'SSE%' 
&nbsp; &nbsp; &nbsp; &nbsp; OR servicename LIKE 'SE%' 
&nbsp; &nbsp; &nbsp; &nbsp; --OR servicename LIKE '%S0BB%' 
&nbsp; &nbsp; &nbsp; &nbsp; --OR servicename &nbsp;substring(ServiceName,0,len(ServiceName)-4) 
&nbsp; &nbsp; &nbsp; &nbsp; 
GROUP BY 
&nbsp; &nbsp; &nbsp; CASE &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN servicename LIKE '%PLUS%' THEN 'SCSCXEPLUS' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN servicename LIKE 'SSE%' THEN 'SSE' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN servicename LIKE 'SE%' THEN 'SE' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --WHEN servicename LIKE '%S0BB%' THEN 'S0BB' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --WHEN servicename &nbsp;substring(ServiceName,0,len(ServiceName)-4)='SCX' THEN 'SCX' &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; END &nbsp; 
 
--SELECT * FROM ##TempData11 
 
&nbsp; &nbsp; &nbsp; &nbsp; DECLARE Defaultval CURSOR FOR 
&nbsp; &nbsp; &nbsp; SELECT Number FROM &nbsp;##TempData11 
&nbsp; &nbsp; &nbsp; OPEN Defaultval 
&nbsp; &nbsp; &nbsp; FETCH NEXT FROM Defaultval INTO @Number 
&nbsp; &nbsp; &nbsp; --Print @Number 
 
&nbsp; 
--SELECT &nbsp;COUNT(ServiceName) FROM tbl_WeeklyLimit Where substring(ServiceName,0,len(ServiceName)-4)='SCX' 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET @SQL = 'Create Table #TableName (' &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; While (@Number&gt;0) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET &nbsp;@SQL=@SQL + 'column'+ CAST(@Number as varchar)+ ' varchar(50) ,' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET @Number = @Number - 1 
&nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END 
 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET @SQL=Substring(@SQL,0,len(@SQL)-1) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SET @SQL=@SQL +')' 
&nbsp; &nbsp; 
&nbsp; 
 
&nbsp; print @SQL 
&nbsp; EXEC (@SQL) 
 
&nbsp; CLOSE Defaultval 
&nbsp; DEALLOCATE Defaultval 
so when i am executing the query i am getting the message like 

</div>

<div>
change 
 
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '##TempData11') 
&nbsp; &nbsp;DROP TABLE &nbsp;##TempData11 
 
to 
 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tempdb..##TempData11' ) ) 
&nbsp; &nbsp;DROP TABLE &nbsp;##TempData11 
 

</div>

<div>
ok ichange the code and executed &nbsp;the fresh code , but i got error message like 
Server: Msg 208, Level 16, State 1, Line 11 
Invalid object name 'sys.objects'. 
&nbsp;for 
.IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tempdb..##TempData11' ) ) 
&nbsp; &nbsp;DROP TABLE &nbsp;##TempData11 
 

</div>

<div>
<div class="content wysiwyg-content">
i am &nbsp;creating a temp table in the database.First time when i am executing the query it is not making any problem , but second time when i am executing the &nbsp;query &nbsp;i am getting the message like 
Server: Msg 2714, Level 16, State 6, Line 13 
There is already an object named '##TempData11' in the database. 
&nbsp; socan any one help me how to check the database that whether the table is present or not , if present then drop the table and then again create the table 
</div>
</div>

i am  creating a temp table in the database.First time when i am executing the query it is not making any problem , but second time when i am executing the  query  i am getting the message like 
Server: Msg 2714, Level 16, State 6, Line 13
There is already an object named '##TempData11' in the database.
  socan any one help me how to check the database that whether the table is present or not , if present then drop the table and then again create the table 

replacing old datatable  in sql server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.