Neulich kam ich in die Verlegenheit ein Script bauen zu müssen, welches mir alle Views in einer MS-SQL-Datenbank zeigt, und für diese dann auch die jeweilige Anzahl an Zeile ausgibt.
set nocount on
declare @ObjectName varchar(100)
declare objectcursor cursor for
select
TABLE_NAME
from information_schema.tables
where 1=1
and table_type = ‚VIEW‘
and table_name not like ’syncob%‘
and table_name not like ‚vvZZ%‘
order by table_name desc
open objectcursor
fetch next from objectcursor into @ObjectName
while @@fetch_status = 0
begin
print @ObjectName + ‚ testing starting‘
declare @start datetime = getdate()
declare @sql nvarchar(300) = ’select count(*) from [‚ + @ObjectName + ‚] with (nolock)‘
–print ‚ ‚ + @sql
exec sp_executesql @sql
declare @End datetime = getdate()
declare @Duration int = datediff(ms, @Start, @End)
print @ObjectName + ‚ testing completed, Duration: ‚ + convert(varchar, @Duration) + ‚ ms‘
fetch next from objectcursor into @ObjectName
end
close objectcursor
deallocate objectcursor