Get list of table names in each database

I came across a situation where i have to gather information about all tables in each database onto a particular server. There were nearly 60 databases each with more than hundred tables. It was impossible for me to run a query for every single database and then merge the results.

In order to achive this, i created a temporary table to store database and table names. With help of undocumented stored procedure 'sp_msForEachDB' i stored the results into temporary table.

CREATE TABLE #t(dbName SYSNAME, tblName SYSNAME);

EXEC sp_msForEachDB 'INSERT #t

SELECT ''?'', TABLE_NAME

FROM [?].INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE=''BASE TABLE'';';

SELECT * FROM #t ORDER BY dbName,tblName;

DROP TABLE #t;

In order to understand 'sp_msForEachDB' command, refer to post 'Traversing
through every database: sp_MSforeachdb'

Traversing through every database: sp_MSforeachdb

This is undocumented stored procedure created by Microsoft.

The SP "sp_MSforeachdb" is found in the "master" database. This SP is used to execute a single or multipel T-SQL statements against every database defined to a SQL Server instance.

Here is the syntax for calling this undocumented SP:

EXEC @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar,
@command2, @command3, @precommand, @postcommand

Where:
@RETURN_VALUE - is the return value which will be set by "sp_MSforeachdb"
@command1 - is the first command to be executed by "sp_MSforeachdb" and is defined as nvarchar(2000)
@replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
@command2 and @command3 are two additional commands that can be run against each database
@precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any database
@postcommand - is also an nvarchar(2000) field used to identify a command to be run after all commands have been processed against all databases.

Example:
Lets say, we have to run "DBCC CHECKDB" command for each database onto server. Run the following code:



DECLARE @cmd1 varchar(500)
DECLARE @cmd2 varchar(500)
SET @cmd1 =
'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''
SET @cmd2 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'
EXEC sp_MSforeachdb @command1=@cmd1,
@command2=@cmd2

How can I find whether the SQL Server 2005 installed is a 64bit or 32bit?

Sometimes a situation arise when few features of SQL Server don't work on 64 bit version and a developer is not aware of which version he is using. In order to know about SQL Server version, you can use

SELECT @@version

This SQL statement will tell you the following information
- SQL Server Version
- Edition
- Processor family - 32 bit or 64 bit

Example is:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Find Nth Record from a table

Sometimes you have to find Nth record from a table in database. There is no such inbuilt function which could return this record. There could be many ways to find the result, I wrote a query which solves the purpose.

SELECT top 1 *
FROM (SELECT top Nth * FROM dim_store) as a
ORDER BY dim_store_id desc

SQL Server - Maximum Sizes

Object	Maximum sizes/numbers	Maximum sizes/numbers	Maximum sizes/numbers
	SQL Server 7 (32-bit)	SQL Server 2000 (32-bit)	SQL Server 2005 (32-bit)
Batch size	65,536 * Network Packet Size	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per short string column	8000	8000	8000
Bytes per text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or XML column	231 -2 bytes/p>	231 -2 bytes/p>	231 -1 bytes/p>
Bytes per GROUP BY, ORDER BY	8060	8060	8060
Bytes per index	900	900	9001
Bytes per foreign key	900	900	??2
Bytes per primary key	900	900	900 - Not listed, but since this will be an index, the index guidelines should apply.
Bytes per row	8060	8060	8060
Bytes in source text of a stored procedure	Lesser of batch size or 250 MB or 128MB3	Lesser of batch size or 250 MB or 128MB3	128MB
Clustered indexes per table	1	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10	10
Columns per index	16	16	164 5
Columns per foreign key	16	16	164
Columns per primary key	16	16	164
Columns per base table	1024	1024	1024
Columns per SELECT statement	4096	4096	4096
Columns per INSERT statement	1024	1024	1024
Connections per client	Maximum value of configured connections (32,767 max)	Maximum value of configured connections (32,767 max)	Maximum value of configured connections (32,767 max)
Database size	1,048,516 terabytes	1,048,516 terabytes	1,048,516 terabytes
Databases per instance of SQL Server	32767	32767	32767
Filegroups per database	256	256	32767
Files per database	32767	32767	32767
File size (data)	32 terabytes	32 terabytes	32 terabytes
File size (log)	32 terabytes	32 terabytes	32 terabytes
Foreign key table references per table	253	253	253
Identifier length (in characters)	128	128	128
Instances per computer	16	16	50
Length of a string containing SQL statements (batch size)	65,536 * Network packet size	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server	Up to 2,147,483,647	Limited only by memory	Limited to 60% of memory
Nested stored procedure levels	32	32	32
Nested subqueries	32	32	no limit, at least according to Beta 2 BOL
Nested trigger levels	32	32	32
Nonclustered indexes per table	249	249	249
Objects concurrently open in an instance of SQL Server	2,147,483,647 per database (depending on available memory)	2,147,483,647 per database (depending on available memory)	2,147,483,647 per database (depending on available memory)
Objects in a database	2147483647	2147483647	2147483647
Parameters per stored procedure	2100	2100	2100
Parameters per user-defined function	2100	2100	2100
REFERENCES per table	253	253	253
Rows per table	Limited by available storage	Limited by available storage	Limited by available storage
Tables per database	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database
Tables per SELECT statement	256	256	256
Triggers per table	Limited by number of objects in a database	Limited by number of objects in a database	Limited by number of objects in a database
UNIQUE indexes or constraints per table	249 nonclustered and 1 clustered	249 nonclustered and 1 clustered	249 nonclustered and 1 clustered
Footnotes
1 - By including nonkey columns in the index, you can exceed the 900 byte limit as these columns (used in covering queries) are not computed as part of the 900 byte limit.
2 - I could not find this listed in the SQL Server 2005 Beta 2 BOL.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp" title="'>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp"> 3 - The Maximum Capacity Specifications shows the less or the batch size or 250MB, however Books Online shows 128MB in the entry for
4 - The 16 column limit is for key columns. Additional columns can be included (as in footnote 1) beyond the 15.
5 - Not valid for XML indexes.