Solved

Max no of tables in sql server db.

Posted on 2006-11-19
8
1,905 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

717 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