There are times as a DBA/Developer when you want to run a SQL query against all databases hosted on an instance. A lot was written about different ways of performing this task. Different people got different preference of the methods used to achieve their goals. I have used three of the methods (while loop, sp_MSforeachdb and cursor). I wouldn’t say one is better than the other apart from sp_MSforeachdb which is undocumented stored procedure and can be obsolete as and when Microsoft decides to drop it. I want the community aware of the 4th ways of performing the task.
1. to use the script as it is replace the mydomain\teshome with your own logon and/or AD group or SQL account.
2. To change the script to perform something else then you need to replace entire code in
"IF NOT EXISTS (SELECT * from sys.sysusers WHERE name = N''mydomain\teshome'')
BEGIN
CREATE USER [mydomain\teshome] FOR LOGIN [mydomain\teshome];
ALTER USER [mydomain\teshome] WITH DEFAULT_SCHEMA=[dbo];
ALTER ROLE [db_datareader] ADD MEMBER [mydomain\teshome];
END"
for instance if you want to query size of all databases you can replace the above script with the following and it should work.
SELECT db_name() as DBName, filegroup_name(groupid) FileGroupName,a.name, Filename,
CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [AllocatedInMB],
CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,''SpaceUsed'')/128.000,2)) AS [UsedInMB],
CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,''SpaceUsed''))/128.000,2)) AS [AvailInMB],
MaxSize = CASE WHEN a.growth = 0 THEN cast(CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) AS VARCHAR) + '' MB''
WHEN a.maxsize = -1 then ''Space/File Max'' ELSE cast (cast (a.maxsize*1.0/128 as decimal(9,2)) as nvarchar(30)) + '' MB'' end,
[%SpaceUsed] = CASE WHEN a.growth = 0
THEN CONVERT(varchar,cast(ROUND(FILEPROPERTY(a.Name,''SpaceUsed'')/128.000,2)/ROUND(a.Size/128.000,2)*100 AS decimal(9,2)) )
WHEN a.maxsize = -1 AND a.growth > 0 THEN ''N/A''
WHEN a.maxsize = 0 THEN ''Review'' ELSE cast (cast(((a.size*1.0)/(a.maxsize*1.0))*100 as decimal(9,2)) as nvarchar(30)) End,
GrowthBy = case when sm.is_percent_growth = 1 then cast(a.growth as varchar)+ ''%''
when a.growth = 0 then ''Not Allowed'' else cast (cast (a.growth*1.0/128 as decimal(9,2)) as nvarchar(30)) + '' MB'' end
FROM dbo.sysfiles a join sys.master_files sm on a.fileid = sm.file_id where sm.database_id = DB_ID()'
