use master
go
 
create function fn_exec_text()
returns nvarchar(4000)
begin
declare @ret nvarchar(4000)
set @ret= N' 
alter view v_statistics_spaceusage_tables

as

SELECT 

a3.name AS [schema_name],

a2.name AS [table_name],

a1.rows as row_count,

(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved_kb,

a1.data * 8 AS data_kb,

(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size_kb,

cast(case when (a1.reserved + ISNULL(a4.reserved,0))=0 then NULL else ((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END)*100.0) / (a1.reserved + ISNULL(a4.reserved,0)) end as decimal(5,2)) as index_pct,

(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused_kb,

cast(case when (a1.reserved + ISNULL(a4.reserved,0)) = 0 then NULL else ((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END)*100.0 / (a1.reserved + ISNULL(a4.reserved,0))) end as decimal(5,2)) AS unused_pct,

IDENT_CURRENT(''['' + db_name() + ''].[''+ a3.name + ''].['' + a2.name + '']'') last_identity,

(select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc where tc.TABLE_SCHEMA = a3.name and tc.TABLE_NAME=a2.name and CONSTRAINT_TYPE = ''primary key'' ) has_pk,

(select count(*) from INFORMATION_SCHEMA.COLUMNS           tc where tc.TABLE_SCHEMA = a3.name and tc.TABLE_NAME=a2.name and tc.DATA_TYPE in(''timestamp'')) has_tstmp,

st.is_published,

--case st.lob_data_space_id when 0 then ''N'' else ''J'' end has_lobs,
(select count(*) from sys.columns sc where sc.object_id=st.object_id) cols,
(select count(*) from sys.columns sc where sc.object_id=st.object_id and (sc.system_type_id in(35,99,34) or (sc.system_type_id in(167,231,165,241) and max_length=-1))) lobs,
st.large_value_types_out_of_row as is_oor,

cast(case when a1.rows=0 then null else a1.data * 8.0 / a1.rows end as decimal(16,4)) data_kb_per_row,
cast(case when a1.rows=0 then null else ((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8) * 8.0 / a1.rows end as decimal(16,4)) index_kb_per_row
 
FROM 

(SELECT 
		ps.object_id
	, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows]
	, SUM (ps.reserved_page_count) AS reserved
	, SUM 
	  (CASE
		WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
		ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
		END
	   ) AS data
	   	
	,SUM (ps.used_page_count) AS used

	FROM sys.dm_db_partition_stats ps
	GROUP BY ps.object_id
) AS a1
LEFT OUTER JOIN
(SELECT 
	it.parent_id
	,SUM(ps.reserved_page_count) AS reserved
	,SUM(ps.used_page_count) AS used
	FROM 
			   sys.dm_db_partition_stats ps
	INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
	WHERE it.internal_type IN (202,204)
	GROUP BY it.parent_id
) AS						a4 ON (a4.parent_id = a1.object_id)

INNER JOIN sys.all_objects	a2  ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas		a3 ON (a2.schema_id = a3.schema_id)
INNER JOIN sys.tables       st on st.object_id=a1.object_id
WHERE a2.type <> N''S'' and a2.type <> N''IT''
and st.is_ms_shipped=0
'

return @ret
end
GO

exec master..sp_MSforeachdb N'
use [?]
if exists(select * from sys.databases	where database_id  = db_id() and is_read_only = 0)
begin
	declare @sql nvarchar(4000)
	select @sql = master.dbo.fn_exec_text()
	exec sp_executesql @sql
end
'

go
drop function fn_exec_text

CREATE PROCEDURE [sp_growing_perf] 

	@database_name sysname, @shema_name sysname, @table_name sysname, @messure_duration_sec decimal(16,4)

AS
BEGIN	
	SET NOCOUNT ON;

    declare @rows1 bigint, @rows2 bigint, @waitto datetime, @sql nvarchar(4000)

	set @shema_name=isnull(@shema_name,'dbo');
	set @waitto=dateadd(ss,@messure_duration_sec,getdate());	
	@shema_name + '.' + @table_name + ''')  and indid in (0,1)'

	--SQL-Versionen: https://sqlserverbuilds.blogspot.de/
	
    if cast(substring(cast(SERVERPROPERTY('productversion') as varchar(20)),1,2) as int)<=10 --bis 2008 R2:
	set @sql = 'SELECT @rows=SUM(CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) FROM ' + @database_name + '.sys.dm_db_partition_stats ps WHERE ps.object_id = object_id(''' + @database_name + '.' + @shema_name + '.' + @table_name + ''')'	
	  else
    set @sql = 'SELECT @rows=SUM(CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) FROM ' + @database_name + '.sys.dm_db_partition_stats ps WHERE ps.object_id NOT IN (SELECT object_id FROM ' + @database_name + '.sys.tables WHERE is_memory_optimized = 1)and ps.object_id = object_id(''' + @database_name + '.' + @shema_name + '.' + @table_name + ''')'
	
	exec sp_executesql @sql, N'@rows bigint OUTPUT', @rows=@rows1 OUTPUT
	waitfor time @waitto;
	exec sp_executesql @sql, N'@rows bigint OUTPUT', @rows=@rows2 OUTPUT

	select @rows1 rows_from, @rows2 rows_to, cast((@rows2-@rows1) / @messure_duration_sec as decimal(12,1)) rows_sec
		
END