[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to generate total number of records in db

Posted on 2016-11-15
35
Medium Priority
?
62 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
[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
  • 19
  • 16
35 Comments
 
LVL 32

Expert Comment

by:Pawan Kumar
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 32

Expert Comment

by:Pawan Kumar
ID: 41889734
Worked for you ?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:michalek19
ID: 41889747
Result
NumberofrowsindDatabse
2342

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

Expert Comment

by:Pawan Kumar
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 32

Expert Comment

by:Pawan Kumar
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 32

Expert Comment

by:Pawan Kumar
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 32

Expert Comment

by:Pawan Kumar
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 32

Expert Comment

by:Pawan Kumar
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 32

Expert Comment

by:Pawan Kumar
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
 
LVL 32

Expert Comment

by:Pawan Kumar
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 32

Expert Comment

by:Pawan Kumar
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 32

Expert Comment

by:Pawan Kumar
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 32

Expert Comment

by:Pawan Kumar
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 32

Accepted Solution

by:
Pawan Kumar earned 2000 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 32

Expert Comment

by:Pawan Kumar
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 32

Expert Comment

by:Pawan Kumar
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 32

Expert Comment

by:Pawan Kumar
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

656 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