Solved

Max no of tables in sql server db.

Posted on 2006-11-19
8
1,900 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now