Solved

How to generate total number of records in db

Posted on 2016-11-15
35
26 Views
Last Modified: 2016-11-17
Hi

How I can pull info about  total number of records in DBase?

Thx,M
0
Comment
Question by:michalek19
  • 19
  • 16
35 Comments
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41888958
Try, you can execute below queries and get the results..

--If you want to get total number of records in a single table.

select count(*) NoOfReocords from a

Open in new window

*** - Note - a is the table name

Output for above query

NoOfReocords
3

--If you want to get total number of records in a database only.

SELECT SUM(rowCnt) [NumberOfRowsIndDatabase] FROM
(	
	SELECT
		  SUM(p.Rows) rowCnt
	FROM 
		  sys.objects o INNER JOIN sys.partitions p ON o.object_id = p.object_id
	WHERE
		  o.type = 'U' AND o.is_ms_shipped = 0x0 AND index_id < 2
	GROUP BY 
			o.schema_id , o.name
)l

Open in new window


Output for above query

NumberOfRowsIndDatabase
6


--If you want to get each table with their row count use below-

SELECT
		  SCHEMA_NAME(o.SCHEMA_ID) + '.' + (o.NAME) TableName,
		  SUM(p.Rows) rowCnt
	FROM 
		  sys.objects o INNER JOIN sys.partitions p ON o.object_id = p.object_id
	WHERE
		  o.type = 'U' AND o.is_ms_shipped = 0x0 AND index_id < 2
	GROUP BY 
			o.schema_id , o.name

Open in new window


Output for above query

TableName      rowCnt
dbo.a                 3
dbo.a1                 3

Hope it helps
0
 

Author Comment

by:michalek19
ID: 41889727
How I can get total number or records per individual database?


--If you want to get total number of records in a database only

SELECT SUM(rowCnt) [NumberOfRowsIndDatabase] FROM
(      
      SELECT
              SUM(p.Rows) rowCnt
      FROM
              sys.objects o INNER JOIN sys.partitions p ON o.object_id = p.object_id
      WHERE
              o.type = 'U' AND o.is_ms_shipped = 0x0 AND index_id < 2
      GROUP BY
                  o.schema_id , o.name
)l
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41889734
Worked for you ?
0
 

Author Comment

by:michalek19
ID: 41889747
Result
NumberofrowsindDatabse
2342

Is this result generated base on all of the database ?
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41889750
it is for a single database. do you want for multiple databases?
0
 

Author Comment

by:michalek19
ID: 41889775
Yes, I need to multiple DBs (I have more then 30 )
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41890736
try..

DECLARE @SQL AS VARCHAR(MAX)=''
DECLARE @dbName AS VARCHAR(1000)=''

IF OBJECT_ID('tempdb..#TempBills') IS NOT NULL
    DROP TABLE #TempBills
       
IF OBJECT_ID('tempdb..#TempBillDetails') IS NOT NULL
    DROP TABLE #TempBillDetails

SELECT * INTO #TempBills FROM sys.databases

CREATE TABLE #TempBillDetails([NumberOfRowsIndDatabase] BIGINT)

WHILE EXISTS(SELECT TOP 1 name FROM #TempBills)

BEGIN

	SELECT TOP 1 @dbName = name FROM #TempBills
       
	SET @SQL = 'INSERT INTO #TempBillDetails
				SELECT ISNULL(SUM(rowCnt),0) [NumberOfRowsIndDatabase] FROM
				(	
					SELECT
						  SUM(p.Rows) rowCnt
					FROM 
						  '+ @dbName  +'.sys.objects o INNER JOIN ' + @dbName +'.sys.partitions p ON o.object_id = p.object_id
					WHERE
						  o.type = ''U'' AND o.is_ms_shipped = 0x0 AND index_id < 2
					GROUP BY 
							o.schema_id , o.name
				)l'

	EXEC (@SQL)

	DELETE FROM #TempBills WHERE name = @dbName

END

SELECT SUM(NumberOfRowsIndDatabase) NumberOfRowsIndDatabase FROM #TempBillDetails

Open in new window


Hope it helps !!
0
 

Author Comment

by:michalek19
ID: 41890742
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41890749
Checking Sir, Meanwhile could you please check the output of this one
?
0
 

Author Comment

by:michalek19
ID: 41890759
I am not at work. I can check this tomorrow morning
0
 

Author Comment

by:michalek19
ID: 41890777
I got result but also I got so error messages

Result :   1338789534


Error messages
78 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 7202, Level 11, State 2, Line 1
Could not find server 'ABSCore' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41890780
These are just messages. Please try below - I have removed the messages..

SET NOCOUNT ON

DECLARE @SQL AS VARCHAR(MAX)=''
DECLARE @dbName AS VARCHAR(1000)=''

IF OBJECT_ID('tempdb..#TempBills') IS NOT NULL
    DROP TABLE #TempBills
       
IF OBJECT_ID('tempdb..#TempBillDetails') IS NOT NULL
    DROP TABLE #TempBillDetails

SELECT * INTO #TempBills FROM sys.databases

CREATE TABLE #TempBillDetails([NumberOfRowsIndDatabase] BIGINT)

WHILE EXISTS(SELECT TOP 1 name FROM #TempBills)

BEGIN

	SELECT TOP 1 @dbName = name FROM #TempBills
       
	SET @SQL = 'INSERT INTO #TempBillDetails
				SELECT ISNULL(SUM(rowCnt),0) [NumberOfRowsIndDatabase] FROM
				(	
					SELECT
						  SUM(p.Rows) rowCnt
					FROM 
						  '+ @dbName  +'.sys.objects o INNER JOIN ' + @dbName +'.sys.partitions p ON o.object_id = p.object_id
					WHERE
						  o.type = ''U'' AND o.is_ms_shipped = 0x0 AND index_id < 2
					GROUP BY 
							o.schema_id , o.name
				)l'

	EXEC (@SQL)

	DELETE FROM #TempBills WHERE name = @dbName

END

SELECT SUM(NumberOfRowsIndDatabase) NumberOfRowsIndDatabase FROM #TempBillDetails

Open in new window

0
 

Author Comment

by:michalek19
ID: 41890783
I got now more errors then before

sg 7202, Level 11, State 2, Line 1
Could not find server 'Axxx' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Msg 117, Level 15, State 1, Line 7
The object name 'ABxxxx.mo.xxx.sys.objects' contains more than the maximum number of prefixes. The maximum is 3.
Msg 117, Level 15, State 1, Line 7
The object name 'ABxx.xxx.xxx.sys.objects' contains more than the maximum number of prefixes. The maximum is 3.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '-'.
Msg 7202, Level 11, State 2, Line 1
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41890787
Are you using linked SERVER ? Are you changing anything in the script ?
0
 

Author Comment

by:michalek19
ID: 41890790
I am not changing anything to the script . I was on the server and I applied script
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41890793
Can you run below and give me the output to check what is happening.

SET NOCOUNT ON

DECLARE @SQL AS VARCHAR(MAX)=''
DECLARE @dbName AS VARCHAR(1000)=''

IF OBJECT_ID('tempdb..#TempBills') IS NOT NULL
    DROP TABLE #TempBills
       
IF OBJECT_ID('tempdb..#TempBillDetails') IS NOT NULL
    DROP TABLE #TempBillDetails

SELECT TOP 1 * INTO #TempBills FROM sys.databases

CREATE TABLE #TempBillDetails([NumberOfRowsIndDatabase] BIGINT)

WHILE EXISTS(SELECT TOP 1 name FROM #TempBills)

BEGIN

	SELECT TOP 1 @dbName = name FROM #TempBills
       
	SET @SQL = 'INSERT INTO #TempBillDetails
				SELECT ISNULL(SUM(rowCnt),0) [NumberOfRowsIndDatabase] FROM
				(	
					SELECT
						  SUM(p.Rows) rowCnt
					FROM 
						  '+ @dbName  +'.sys.objects o INNER JOIN ' + @dbName +'.sys.partitions p ON o.object_id = p.object_id
					WHERE
						  o.type = ''U'' AND o.is_ms_shipped = 0x0 AND index_id < 2
					GROUP BY 
							o.schema_id , o.name
				)l'

	PRINT (@SQL)

	DELETE FROM #TempBills WHERE name = @dbName

END

Open in new window

0
 

Author Comment

by:michalek19
ID: 41890794
only message

INSERT INTO #TempBillDetails
                        SELECT ISNULL(SUM(rowCnt),0) [NumberOfRowsIndDatabase] FROM
                        (      
                              SELECT
                                      SUM(p.Rows) rowCnt
                              FROM
                                      master.sys.objects o INNER JOIN master.sys.partitions p ON o.object_id = p.object_id
                              WHERE
                                      o.type = 'U' AND o.is_ms_shipped = 0x0 AND index_id < 2
                              GROUP BY
                                          o.schema_id , o.name
                        )l
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41890795
Now run this only..

SELECT ISNULL(SUM(rowCnt),0) [NumberOfRowsIndDatabase] FROM
                        (      
                              SELECT
                                      SUM(p.Rows) rowCnt
                              FROM 
                                      master.sys.objects o INNER JOIN master.sys.partitions p ON o.object_id = p.object_id
                              WHERE
                                      o.type = 'U' AND o.is_ms_shipped = 0x0 AND index_id < 2
                              GROUP BY 
                                          o.schema_id , o.name
                        )l

Open in new window

0
 

Author Comment

by:michalek19
ID: 41890800
number of rows in database:  2342

I believe I have more # of rows then 2342
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41890804
OPEN A NEW Query Window and then execute below-

SET NOCOUNT ON

DECLARE @SQL AS VARCHAR(MAX)=''
DECLARE @dbName AS VARCHAR(1000)=''

IF OBJECT_ID('tempdb..#TempBills') IS NOT NULL
    DROP TABLE #TempBills
       
IF OBJECT_ID('tempdb..#TempBillDetails') IS NOT NULL
    DROP TABLE #TempBillDetails

SELECT * INTO #TempBills FROM sys.databases

CREATE TABLE #TempBillDetails([NumberOfRowsIndDatabase] BIGINT)

WHILE EXISTS(SELECT TOP 1 name FROM #TempBills)

BEGIN

	SELECT TOP 1 @dbName = name FROM #TempBills
       
	SET @SQL = 'INSERT INTO #TempBillDetails
				SELECT ISNULL(SUM(rowCnt),0) [NumberOfRowsIndDatabase] FROM
				(	
					SELECT
						  SUM(p.Rows) rowCnt
					FROM 
						  '+ @dbName  +'.sys.objects o INNER JOIN ' + @dbName +'.sys.partitions p ON o.object_id = p.object_id
					WHERE
						  o.type = ''U'' AND o.is_ms_shipped = 0x0 AND index_id < 2
					GROUP BY 
							o.schema_id , o.name
				)l'

	EXEC (@SQL)

	DELETE FROM #TempBills WHERE name = @dbName

END

SELECT SUM(NumberOfRowsIndDatabase) NumberOfRowsIndDatabase FROM #TempBillDetails

Open in new window

0
 

Author Comment

by:michalek19
ID: 41890805
Msg 7202, Level 11, State 2, Line 1
Could not find server 'Axxx' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Msg 117, Level 15, State 1, Line 7
The object name 'ABSCxxxxx.sys.objects' contains more than the maximum number of prefixes. The maximum is 3.
Msg 117, Level 15, State 1, Line 7
The object name xxxxxx.Import.sys.objects' contains more than the maximum number of prefixes. The maximum is 3.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '-'.
0
 

Author Comment

by:michalek19
ID: 41890806
but I got result   1467489597
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41890810
Try provide your servername here DECLARE @ServerName AS VARCHAR(MAX)=''

SET NOCOUNT ON

DECLARE @ServerName AS VARCHAR(MAX)=''
DECLARE @SQL AS VARCHAR(MAX)=''
DECLARE @dbName AS VARCHAR(1000)=''

IF OBJECT_ID('tempdb..#TempBills') IS NOT NULL
    DROP TABLE #TempBills
       
IF OBJECT_ID('tempdb..#TempBillDetails') IS NOT NULL
    DROP TABLE #TempBillDetails

SELECT * INTO #TempBills FROM sys.databases

CREATE TABLE #TempBillDetails([NumberOfRowsIndDatabase] BIGINT)

WHILE EXISTS(SELECT TOP 1 name FROM #TempBills)

BEGIN

	SELECT TOP 1 @dbName = name FROM #TempBills
       
	SET @SQL = 'INSERT INTO #TempBillDetails
				SELECT ISNULL(SUM(rowCnt),0) [NumberOfRowsIndDatabase] FROM
				(	
					SELECT
						  SUM(p.Rows) rowCnt
					FROM 
						  ['+ @ServerName + '].' + @dbName  +'.sys.objects o INNER JOIN [' + @ServerName + '].'  + @dbName +'.sys.partitions p ON o.object_id = p.object_id
					WHERE
						  o.type = ''U'' AND o.is_ms_shipped = 0x0 AND index_id < 2
					GROUP BY 
							o.schema_id , o.name
				)l'

	EXEC (@SQL)

	DELETE FROM #TempBills WHERE name = @dbName

END

SELECT SUM(NumberOfRowsIndDatabase) NumberOfRowsIndDatabase FROM #TempBillDetails

Open in new window

0
 

Author Comment

by:michalek19
ID: 41890813
result

Msg 1038, Level 15, State 4, Line 6
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 4, Line 7
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 4, Line 6
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 4, Line 7
0
 

Author Comment

by:michalek19
ID: 41890814
null
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41890821
Pass ServerName in this parameter... Replace Server Name with '' and then run

DECLARE @ServerName AS VARCHAR(MAX)=''
0
 

Author Comment

by:michalek19
ID: 41890824
I got numberofrowindb
1469150650


and many errors

Msg 117, Level 15, State 1, Line 7
The object name 'pc-wp1.Axxxx.System.sys.objects' contains more than the maximum number of prefixes. The maximum is 3.
Msg 117, Level 15, State 1, Line 7
The object name 'pc-wp1.Axxxx.MIL.Model.sys.objects' contains more than the maximum number of prefixes. The maximum is 3.
Msg 117, Level 15, State 1, Line 7
The object name 'pc-wp1.Axxxx.MIL.Import.sys.objects' contains more than the maximum number of prefixes. The maximum is 3.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '-'.
0
 
LVL 18

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
ID: 41890839
Seems like you have .(Dot) in DB name. Try this.

SET NOCOUNT ON

DECLARE @ServerName AS VARCHAR(MAX)=''
DECLARE @SQL AS VARCHAR(MAX)=''
DECLARE @dbName AS VARCHAR(1000)=''

IF OBJECT_ID('tempdb..#TempBills') IS NOT NULL
    DROP TABLE #TempBills
       
IF OBJECT_ID('tempdb..#TempBillDetails') IS NOT NULL
    DROP TABLE #TempBillDetails

SELECT * INTO #TempBills FROM sys.databases

CREATE TABLE #TempBillDetails([NumberOfRowsIndDatabase] BIGINT)

WHILE EXISTS(SELECT TOP 1 name FROM #TempBills)

BEGIN

	SELECT TOP 1 @dbName = name FROM #TempBills
       
	SET @SQL = 'INSERT INTO #TempBillDetails
				SELECT ISNULL(SUM(rowCnt),0) [NumberOfRowsIndDatabase] FROM
				(	
					SELECT
						  SUM(p.Rows) rowCnt
					FROM 
						  ['+ @ServerName + '].' + '[' + @dbName  +'].sys.objects o INNER JOIN [' + @ServerName + '].['  + @dbName +'].sys.partitions p ON o.object_id = p.object_id
					WHERE
						  o.type = ''U'' AND o.is_ms_shipped = 0x0 AND index_id < 2
					GROUP BY 
							o.schema_id , o.name
				)l'

	EXEC (@SQL)

	DELETE FROM #TempBills WHERE name = @dbName

END

SELECT SUM(NumberOfRowsIndDatabase) NumberOfRowsIndDatabase FROM #TempBillDetails

Open in new window

0
 

Author Comment

by:michalek19
ID: 41891329
What does it .dot? Is it bad ? What .dot does to database?
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41891340
Not bad. I have put square brackets actually.
0
 

Author Comment

by:michalek19
ID: 41891539
Msg 1038, Level 15, State 4, Line 6
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 4, Line 7
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 4, Line 6
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
Msg 1038, Level 15, State 4, Line 7
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41891544
Have you given Server Name , Put server name between single quotes and then try..

DECLARE @ServerName AS VARCHAR(MAX)=''
0
 

Author Comment

by:michalek19
ID: 41891560
Great this time no errors

I got output
3,009,249,205

So this number means total number of records in all databases.

Can you  clarify
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41891563
Yes exactly !! Great work.. Finally we can close this..
0
 

Author Closing Comment

by:michalek19
ID: 41891616
Thank you so much for your help
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 90
STDEVP in SQL 2 38
SQL Help - 12 42
Sql query 34 22
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 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

20 Experts available now in Live!

Get 1:1 Help Now