[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

How to generate total number of records in db

Hi

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

Thx,M
0
michalek19
Asked:
michalek19
  • 19
  • 16
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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
 
michalek19Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Worked for you ?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
michalek19Author Commented:
Result
NumberofrowsindDatabse
2342

Is this result generated base on all of the database ?
0
 
Pawan KumarDatabase ExpertCommented:
it is for a single database. do you want for multiple databases?
0
 
michalek19Author Commented:
Yes, I need to multiple DBs (I have more then 30 )
0
 
Pawan KumarDatabase ExpertCommented:
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
 
michalek19Author Commented:
0
 
Pawan KumarDatabase ExpertCommented:
Checking Sir, Meanwhile could you please check the output of this one
?
0
 
michalek19Author Commented:
I am not at work. I can check this tomorrow morning
0
 
michalek19Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
michalek19Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Are you using linked SERVER ? Are you changing anything in the script ?
0
 
michalek19Author Commented:
I am not changing anything to the script . I was on the server and I applied script
0
 
Pawan KumarDatabase ExpertCommented:
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
 
michalek19Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
michalek19Author Commented:
number of rows in database:  2342

I believe I have more # of rows then 2342
0
 
Pawan KumarDatabase ExpertCommented:
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
 
michalek19Author Commented:
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
 
michalek19Author Commented:
but I got result   1467489597
0
 
Pawan KumarDatabase ExpertCommented:
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
 
michalek19Author Commented:
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
 
michalek19Author Commented:
null
0
 
Pawan KumarDatabase ExpertCommented:
Pass ServerName in this parameter... Replace Server Name with '' and then run

DECLARE @ServerName AS VARCHAR(MAX)=''
0
 
michalek19Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
michalek19Author Commented:
What does it .dot? Is it bad ? What .dot does to database?
0
 
Pawan KumarDatabase ExpertCommented:
Not bad. I have put square brackets actually.
0
 
michalek19Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Have you given Server Name , Put server name between single quotes and then try..

DECLARE @ServerName AS VARCHAR(MAX)=''
0
 
michalek19Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Yes exactly !! Great work.. Finally we can close this..
0
 
michalek19Author Commented:
Thank you so much for your help
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 19
  • 16
Tackle projects and never again get stuck behind a technical roadblock.
Join Now