Get Sql Server database file's max size limit with db_reader permissions

Some time ago I wrote how to get current db size in Sql Server with limited db_reader permissions (see Calculate Sql Server database size having only db_reader permissions on target database). In this post I will show how you can also get max size limit of db file also having only db_reader permissions.

As you probably know in Sql Server we may set limit on db file size (and on db transaction log file size) using the following commands (in example below we limit both files sizes to 100Mb):

ALTER DATABASE {db}
MODIFY FILE (NAME = {filename}, MAXSIZE = 100MB);
GO

ALTER DATABASE {db}
MODIFY FILE (NAME = [{filename}.Log], MAXSIZE = 100MB);
GO

As result if we will check Database properties > Files - we will see these limits on both files:

In order to get these limits programmatically using db_reader permissions we should use another system stored procedure sp_helpdb and provider database name as parameter:

exec sp_helpdb N'{databaseName}'

This stored procedure returns 2 result sets. In 2nd result set it returns field maxsize which returns max size limit for db file. Here is the code which reads maxsize field from result of sp_helpdb proc:

using (var connection = new SqlConnection(connectionString))
{
	connection.Open();
	using (var cmd = connection.CreateCommand())
	{
		cmd.CommandText = $"exec sp_helpdb N'{connection.Database}'";
		using (var reader = cmd.ExecuteReader())
		{
			if (reader.NextResult())
			{
				if (reader.Read())
				{
					return reader["maxsize"] as string;
				}
			}
		}
	}
}

For database used in example above it will return string "100 Mb". Hope that it will help someone.