January 24, 2017 at 10:07 am
All,
I'm working on a proc to do a recordcount of all tables and views in a database to compare to another set of tables/views in a different database as an auditing process for our ETL packages. I know I can use this: EXEC sp_MSForEachTable @command1='INSERT #Sourcecounts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
To get table counts, but there's no corresponding way to get rec counts from views.
I've come up with this script to try and remedy that. But I'm running into two problems,
1: The Dynamic SQL is not putting the reccount into the Variable
2: I'm getting the error :
Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.
What am I doing wrong?
Here's the basic Script that I'm trying to run. Once I have it working create an SP.
If there's a better way of doing this I'm open to suggestions.
/**** Variables ******/
DECLARE
@RecCount AS INT
,@LoadedRecs AS INT
,@LoopCounter INT = 1
,@Schema_Name AS VARCHAR(50)
,@Object_Name AS VARCHAR(100)
,@TableName AS VARCHAR(150)
,@SQL AS NVARCHAR(MAX)
--IF OBJECT_ID('tempdb..#TablesViews') IS NOT NULL
/*Then it exists*/
DROP TABLE #TablesViews
CREATE TABLE #TablesViews
(
ObjectType VARCHAR(1)
,Schema_Name VARCHAR(50)
,Object_Name VARCHAR(100)
)
DROP TABLE #TablesViewsCurrent
CREATE TABLE #TablesViewsCurrent
(
ObjectType VARCHAR(1)
,Schema_Name VARCHAR(50)
,Object_Name VARCHAR(100)
,RN INT NOT NULL
)
DROP TABLE #RecCounts
CREATE TABLE #RecCounts
(
ObjectType VARCHAR(1)
,Schema_Name VARCHAR(50)
,Object_Name VARCHAR(100)
,RecCount INT
)
INSERT INTO #TablesViews
SELECT
'V' AS ObjectType
,SCHEMA_NAME(schema_id) AS schema_name
,name AS Object_name
FROM sys.views
UNION ALL
SELECT
'T' AS ObjectType
,SCHEMA_NAME(schema_id) AS schema_name
,name AS Object_Name
FROM sys.tables
INSERT INTO #TablesViewsCurrent
SELECT
*
,ROW_NUMBER() OVER(ORDER BY ObjectType,Schema_Name,Object_Name) RN
FROM #TablesViews
SET @LoadedRecs =
(
SELECT COUNT(*) cnt FROM #TablesViews WITH (NOLOCK)
)
WHILE @LoopCounter < @LoadedRecs + 1
BEGIN
SELECT
ObjectType
,Schema_Name
,Object_Name
,Rn
INTO #CurrentRecord
FROM #TablesViewsCurrent
WHERE Rn = @LoopCounter
SET @Schema_Name = (SELECT Schema_Name FROM #CurrentRecord)
SET @Object_Name = (SELECT Object_Name FROM #CurrentRecord)
SET @TableName = (@Schema_Name + '.' + @Object_Name)
SET @SQL = 'Select @RecCount = Count(*) FROM ' + @TableName
EXECUTE sp_executesql @SQL, '@RecCount int OUTPUT' , @RecCount = @RecCount OUTPUT
SELECT @SQL -- To Check Dynamic SQL is generating correctly
SELECT @RecCount -- To check the Record count
--SELECT * FROM #CurrentRecord
INSERT INTO #RecCounts
SELECT
cr.ObjectType
,cr.Schema_Name
,cr.Object_Name
,@RecCount
FROM #CurrentRecord cr
SET @LoopCounter = @LoopCounter + 1
DROP TABLE #CurrentRecord
END
SELECT * FROM #RecCounts
January 24, 2017 at 10:31 am
i believe the specific error/issue is here:@command1='INSERT #Sou
it has to be marked with teh N to tell it it is nvarchar@command1=N'INSERT #Sou
however there is a much better way to get row counts by querying the DMV's for the number of rows; the count of rows are already materialized and kept track of:
SELECT schema_name(o.schema_id) AS SchemaName,
o.NAME AS ObjectName,
SUM(ps.row_count) AS TheCount,
'SELECT * FROM '
+ Quotename(schema_name(o.schema_id)) + '.'
+ Quotename(o.NAME) AS cmd
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
GROUP BY
schema_name(o.schema_id),
o.NAME
Lowell
January 24, 2017 at 10:41 am
I would take a shortcut for the tables and get the information that is already stored in SQL Server. Then I would just need to count the rows in the views, but I'd only call the sp_executesql procedure once.
To get the count of the views, I'm using a method explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
--Get counts of the TABLES
INSERT INTO #RecCounts(ObjectType,[Schema_Name],[Object_Name],RecCount)
SELECT t.ObjectType, t.[Schema_Name], t.[Object_Name], SUM(s.row_count) AS Row_Count
FROM #TablesViews t
JOIN sys.tables o ON t.[Schema_Name] = SCHEMA_NAME(o.[schema_id])
AND t.[Object_Name] = o.name
JOIN sys.dm_db_partition_stats s ON o.[object_id] = s.[object_id]
WHERE index_id IN(0,1) --heaps or clustered indexes
AND t.ObjectType = 'T'
GROUP BY t.ObjectType, t.[Schema_Name], t.[Object_Name]
--Get counts of the VIEWS
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = STUFF( (SELECT 'UNION ALL ' + CHAR(10)
+ N'Select ''V'', '
+ QUOTENAME([Schema_Name], '''') + N', '
+ QUOTENAME([Object_Name], '''') + N', '
+ N'Count(*) FROM ' + QUOTENAME([Schema_Name]) + N'.' + QUOTENAME(o.name) + CHAR(10)
FROM #TablesViews t
JOIN sys.views o ON t.[Schema_Name] = SCHEMA_NAME(o.[schema_id])
AND t.[Object_Name] = o.name
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 1, 11, '')
INSERT INTO #RecCounts(ObjectType,[Schema_Name],[Object_Name],RecCount)
EXEC sp_executesql @SQL;
SELECT * FROM #RecCounts
Note that I'm joining to sys.views to keep the code safe, as well as QUOTENAME.
And to answer the question about the error, you just needed to change the parameter definition string to make it unicode by adding an N at the beginning.
EXECUTE sp_executesql @SQL, N'@RecCount int OUTPUT' , @RecCount = @RecCount OUTPUT
January 24, 2017 at 10:44 am
craig.bobchin - Tuesday, January 24, 2017 10:07 AM
i believe the specific error/issue is here:@command1='INSERT #Sou
it has to be marked with teh N to tell it it is nvarchar@command1=N'INSERT #Sou
however there is a much better way to get row counts by querying the DMV's for the number of rows; the count of rows are already materialized and kept track of:
SELECT schema_name(o.schema_id) AS SchemaName,
o.NAME AS ObjectName,
SUM(ps.row_count) AS TheCount,
'SELECT * FROM '
+ Quotename(schema_name(o.schema_id)) + '.'
+ Quotename(o.NAME) AS cmd
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
GROUP BY
schema_name(o.schema_id),
o.NAME
I tried it, and it looks like I need to talk with my DBA about updating my permissions. I get this error:
Msg 262, Level 14, State 1, Line 1
VIEW DATABASE STATE permission denied in database 'Dwbi_Staging_3f'.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
January 24, 2017 at 10:52 am
Luis Cazares - Tuesday, January 24, 2017 10:41 AMI would take a shortcut for the tables and get the information that is already stored in SQL Server. Then I would just need to count the rows in the views, but I'd only call the sp_executesql procedure once.
To get the count of the views, I'm using a method explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
--Get counts of the TABLES
INSERT INTO #RecCounts(ObjectType,[Schema_Name],[Object_Name],RecCount)
SELECT t.ObjectType, t.[Schema_Name], t.[Object_Name], SUM(s.row_count) AS Row_Count
FROM #TablesViews t
JOIN sys.tables o ON t.[Schema_Name] = SCHEMA_NAME(o.[schema_id])
AND t.[Object_Name] = o.name
JOIN sys.dm_db_partition_stats s ON o.[object_id] = s.[object_id]
WHERE index_id IN(0,1) --heaps or clustered indexes
AND t.ObjectType = 'T'
GROUP BY t.ObjectType, t.[Schema_Name], t.[Object_Name]--Get counts of the VIEWS
DECLARE @SQL NVARCHAR(MAX);SELECT @SQL = STUFF( (SELECT 'UNION ALL ' + CHAR(10)
+ N'Select ''V'', '
+ QUOTENAME([Schema_Name], '''') + N', '
+ QUOTENAME([Object_Name], '''') + N', '
+ N'Count(*) FROM ' + QUOTENAME([Schema_Name]) + N'.' + QUOTENAME(o.name) + CHAR(10)
FROM #TablesViews t
JOIN sys.views o ON t.[Schema_Name] = SCHEMA_NAME(o.[schema_id])
AND t.[Object_Name] = o.name
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 1, 11, '')INSERT INTO #RecCounts(ObjectType,[Schema_Name],[Object_Name],RecCount)
EXEC sp_executesql @SQL;SELECT * FROM #RecCounts
Note that I'm joining to sys.views to keep the code safe, as well as QUOTENAME.And to answer the question about the error, you just needed to change the parameter definition string to make it unicode by adding an N at the beginning.
EXECUTE sp_executesql @SQL, N'@RecCount int OUTPUT' , @RecCount = @RecCount OUTPUT
I tried it, and it looks like I need to talk with my DBA about updating my permissions. I get this error:
Msg 262, Level 14, State 1, Line 1
VIEW DATABASE STATE permission denied in database 'Dwbi_Staging_3f'.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
Is there a way to do this without getting the permissions?
January 24, 2017 at 10:57 am
Just one small problem with the code posted above using sys.dm_db_partition_stats , it doesn't take into account the possibility of actual partitioned tables.
January 24, 2017 at 10:59 am
Okay I got it working with changing the @SQL and output vars to NVarChars.
Thanks.
January 24, 2017 at 11:02 am
craig.bobchin - Tuesday, January 24, 2017 10:52 AMLuis Cazares - Tuesday, January 24, 2017 10:41 AMI would take a shortcut for the tables and get the information that is already stored in SQL Server. Then I would just need to count the rows in the views, but I'd only call the sp_executesql procedure once.
To get the count of the views, I'm using a method explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
--Get counts of the TABLES
INSERT INTO #RecCounts(ObjectType,[Schema_Name],[Object_Name],RecCount)
SELECT t.ObjectType, t.[Schema_Name], t.[Object_Name], SUM(s.row_count) AS Row_Count
FROM #TablesViews t
JOIN sys.tables o ON t.[Schema_Name] = SCHEMA_NAME(o.[schema_id])
AND t.[Object_Name] = o.name
JOIN sys.dm_db_partition_stats s ON o.[object_id] = s.[object_id]
WHERE index_id IN(0,1) --heaps or clustered indexes
AND t.ObjectType = 'T'
GROUP BY t.ObjectType, t.[Schema_Name], t.[Object_Name]--Get counts of the VIEWS
DECLARE @SQL NVARCHAR(MAX);SELECT @SQL = STUFF( (SELECT 'UNION ALL ' + CHAR(10)
+ N'Select ''V'', '
+ QUOTENAME([Schema_Name], '''') + N', '
+ QUOTENAME([Object_Name], '''') + N', '
+ N'Count(*) FROM ' + QUOTENAME([Schema_Name]) + N'.' + QUOTENAME(o.name) + CHAR(10)
FROM #TablesViews t
JOIN sys.views o ON t.[Schema_Name] = SCHEMA_NAME(o.[schema_id])
AND t.[Object_Name] = o.name
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 1, 11, '')INSERT INTO #RecCounts(ObjectType,[Schema_Name],[Object_Name],RecCount)
EXEC sp_executesql @SQL;SELECT * FROM #RecCounts
Note that I'm joining to sys.views to keep the code safe, as well as QUOTENAME.And to answer the question about the error, you just needed to change the parameter definition string to make it unicode by adding an N at the beginning.
EXECUTE sp_executesql @SQL, N'@RecCount int OUTPUT' , @RecCount = @RecCount OUTPUTI tried it, and it looks like I need to talk with my DBA about updating my permissions. I get this error:
Msg 262, Level 14, State 1, Line 1
VIEW DATABASE STATE permission denied in database 'Dwbi_Staging_3f'.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.Is there a way to do this without getting the permissions?
Use the option I posted for views, but use sys.objects instead and use the ObjectType column instead of the constant.
Asking your DBA for permissions wouldn't hurt. You can justify by saying that it's a read only access and would reduce the read load on the server. Unless he's stubborn, there shouldn't be a reason to avoid it. It might be possible to do this through a stored procedure that wouldn't require that you request additional permissions, just get them through the SP (I think I wasn't able to explain myself on this).
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply