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
Solved

How to generate total number of records in db

Posted on 2016-11-15
35
53 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 28

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 28

Expert Comment

by:Pawan Kumar
ID: 41889734
Worked for you ?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:michalek19
ID: 41889747
Result
NumberofrowsindDatabse
2342

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

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 28

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 28

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 28

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 28

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 28

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 28

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 28

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 28

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 28

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 28

Accepted Solution

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

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 28

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 28

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

861 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