Solved

Max no of tables in sql server db.

Posted on 2006-11-19
8
1,904 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
8 Comments
 
LVL 33

Accepted Solution

by:
sajuks earned 50 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 50 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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 50 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 50 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 50 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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

726 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