To All,
We have a "disgruntled development DBA" and a "smart-aleck
developer"(Oh my! Phew!) that complains about using sp_msforeachdb and
sp_msforeachtable. They claim they both use "cursors" underneath the
covers and they are undocumented. They also claim that dynamic sql is
also bad (security issue)! Hehe. However, they provided the code below
using dynamic sql! Hehe. Hehe. They are just killing me! Hehe. Hehe.


DECLARE @sql1 varchar(8000), @sql2 varchar(8000)
SELECT @sql1 = ISNULL(@sql1,'') + 'dbcc UPDATEUSAGE(' + name + ') WITH
COUNT_ROWS ',
@sql2 = ISNULL(@sql2 + ' UNION ALL','') + '
SELECT ''' + name + ''' db, o.name tbl,i.rows FROM ' + name
+'..sysobjects o JOIN ' + name + '..sysindexes i ON o.id=i.id WHERE
o.xtype=''U'' AND i.indid<2'
FROM master..sysdatabases
WHERE NAME NOT IN ('master', 'msdb', 'model', 'tempdb',
'AdventureWorks','ReportServer', 'ReportServerTempDB')
ORDER BY name
EXEC(@sql1)
EXEC(@sql2)


Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Jeanine Blomberg is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]

MS Sql Server LazyDBA home page