Solved

How to generate total number of records in db

Posted on 2016-11-15
35
39 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 23

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 23

Expert Comment

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

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 23

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 23

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 23

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 23

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 23

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 23

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 23

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 23

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 23

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 23

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 23

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 23

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 23

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Job Dependencies in SQL Server Agent: 1 30
Filtered index 5 55
MS SQL Inner Join - Multiple Join Parameters 2 17
SQL Server Question 5 25
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

932 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

11 Experts available now in Live!

Get 1:1 Help Now