Max no of tables in sql server db.

Is there any max limit for the number of tables allowed to create in a sql database ????

If yes, how should i change it?

niger007Asked:
Who is Participating?
 
sajuksConnect With a Mentor Commented:
From BOL,
check under "Maximum Capacity Specifications"
The sum of the number of all these objects in a database cannot exceed 2,147,483,647.
0
 
niger007Author Commented:

Could you give me a more precise info ???
 
Thks in advance...
0
 
HillwaaaConnect With a Mentor Commented:
Hi niger007,

(no points for me)

See http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx

Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.

Cheers!
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
niger007Author Commented:
Which option in my enterprise manager will allow me to check the "Maximum Capacity Specification"... i knw my comment sounds stupid but i need a solution indeed.

0
 
Gautham JanardhanConnect With a Mentor Commented:
use [urdatabase]
select * from sysobjects

xtype is a column in sysobjects

the values it can hold

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure


select count(*) from sysobjects where xtype = 'u' will give count of usertables
0
 
NightmanConnect With a Mentor CTOCommented:
Put simply, there is no practical limit on the amount of tables you can have. Considering that SQL caters for 2 billion objects, if you simply took the amount of possible objet types (16) and divide by that you STILL have 134,217,727 - a very large number.

To find that in your documentation, simply go Start-->Program Files-->Microsoft SQL Server (or SQL Server 2005, depending on your version) and click Books Online (if you have installed it from the CD, otherwise download it). For SQL 2005, the documentation is in a sub-folder called documentation.
0
 
imran_fastConnect With a Mentor Commented:
Hi number of tables are not limited but number of objects are limited which include triggers and views and et.c

Tables per database Limited by number of objects in a database4


Columns per index 16
Columns per foreign key 16
Columns per primary key 16
Columns per base table,024
Columns per SELECT statement 4,096
Columns per INSERT statement 1,024

Database size 1,048,516 TB3
Databases per instance of SQL Server 32,767
Filegroups per database 256
Files per database 32,767
File size (data) 32 TB
File size (log) 32 TB
Foreign key table references per table  253
Identifier length (in characters)  128
Nested stored procedure levels 32
Nested subqueries 32
Nested trigger levels 32
Nonclustered indexes per table 249
Tables per SELECT statement 256
Triggers per table  Limited by number of objects in a database4
UNIQUE indexes or constraints per table 249 nonclustered and 1 clustered
0
 
niger007Author Commented:
Experts, Thanks a lot for your valuable inputs.

0
All Courses

From novice to tech pro — start learning today.