August 29, 2001 at 10:35 pm
How can you interogate SQL Server Database properties (eg. size etc) from either VB (pro) or access or query.
August 31, 2001 at 1:17 pm
You can obtain a significant amount of information on SQL Server objects through SQL-DMO. VB and VBscript is a great way to use this object model.
Sean
August 31, 2001 at 4:48 pm
DMO is definitely the best way to proceed. I've got several articles on here if you decide to take that path. Here is some sample code to show the db size:
'8/31/01 Andy Warren
' Demonstrate how to retrieve the size of all databases
'or one particular database. Set a reference to MS SQL-DMO in
'VB, or change the 'New' to CreateObject if you're using VBScript
'and remove the with/end with block.
Dim oServer As SQLDMO.SQLServer
Dim oDB As SQLDMO.Database
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect
End With
'prints all of them
For Each oDB In oServer.Databases
Debug.Print oDB.Name, oDB.Size
Next
'this is how you do one, works as long
'as the db exists
Debug.Print "Look up size for Master: " & oServer("Master").Size
oServer.DisConnect
Set oServer = Nothing
Another method would be use sp_spaceused. You can run either from Query Analyzer or from ADO (just execute like any other stored proc, then check the recordset for the results). An easy way to see the space used for all db's using sp_spaceused is like this:
sp_msforeachdb 'use ?;exec sp_spaceused '
Andy
September 2, 2001 at 10:58 pm
Thanks, But I need to run theVB App on an NT Client (which does not have SQL Server installed). As I understand SQLDMO is installed as part of SQL SErver.
September 3, 2001 at 10:13 am
The sp_spaceused method will work as long as you have a valid login. DMO is not just for servers, I havent had time to figure out if there is a more direct way to load it on a workstation, but a simple way is to load the personal version of SQL Server. Its free if you've got a server version licensed. Could probably also load MSDE as an alternative.
Andy
December 5, 2002 at 9:32 pm
quote:
How can you interogate SQL Server Database properties (eg. size etc) from either VB (pro) or access or query.
If you just want the used size, you can look at the .ldf, .mdf files.
If you want data base element sizes, you canuse the information_schema views for the specific objects or you can use ADOX for tons of info in a nice ADO interface.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply