?
Solved

Max no of tables in sql server db.

Posted on 2006-11-19
8
Medium Priority
?
1,907 Views
Last Modified: 2008-01-09
Is there any max limit for the number of tables allowed to create in a sql database ????

If yes, how should i change it?

0
Comment
Question by:niger007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 33

Accepted Solution

by:
sajuks earned 200 total points
ID: 17976894
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
 

Author Comment

by:niger007
ID: 17976940

Could you give me a more precise info ???
 
Thks in advance...
0
 
LVL 16

Assisted Solution

by:Hillwaaa
Hillwaaa earned 200 total points
ID: 17976956
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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

Author Comment

by:niger007
ID: 17976993
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
 
LVL 29

Assisted Solution

by:Gautham Janardhan
Gautham Janardhan earned 200 total points
ID: 17977005
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
 
LVL 29

Assisted Solution

by:Nightman
Nightman earned 200 total points
ID: 17977367
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
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 200 total points
ID: 17977393
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
 

Author Comment

by:niger007
ID: 17992520
Experts, Thanks a lot for your valuable inputs.

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question