Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
matt_calhoon
Posting Yak Master
235 Posts |
 Posted - 2008-04-10 : 22:55:54
|
| Hi there,I want to create an SQL Function that checks if a table exists and returns true or false. I will pass this function a paramter (say @COMPANYID) e.g.if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblmyTableName_' + @COMPANYID) and OBJECTPROPERTY(id, N'IsUserTable') = 1)how would I write this so the query is dynamically executed and I can get a value of true or false back? |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-04-10 : 23:09:29
|
| You are going down the wrong path here. Why would you want to create one table per companyID? I bet you would be better off with having a single tblmyTableName with a COMPANYID column.Anyway if you really insist on doing this then just do a count which will return you 0 or 1 and you can compare against that. |
 |
|
|
matt_calhoon
Posting Yak Master
235 Posts |
Posted - 2008-04-10 : 23:27:15
|
| there are only 3 or 4 companies that get their own table and it is created for horizontal partitioning due to the huge amount of data in the tables and the length of time it takes to dynamically create rankings etc on the table...Long story.A script example would be appreciated - anyone? |
|
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2008-04-11 : 00:40:20
|
| try this Declare @Count Int,@companyId IntSelect @CompanyId = 2Select @Count = Count(1) From sys.sysobjects Where xtype = 'U' and name = 'tblmyTableName_' + cast(@companyId as varchar(10)) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-11 : 04:58:08
|
| or make use of information_schema.tables viewMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Dynamically check if a Table Exists