![]() ![]() Td= CONVERT(NVARCHAR,(Sum(Cast(masterfiles.size AS BIGINT)) * 8 / 1024)),' ' Td = CONVERT(DATETIME, dbs.create_date),' ' , The SQL Code to generate the HTML table that contains the list of databases and their size is following: DECLARE nvarchar(max) In this demo, we are using dynamic management views. You can read more about the sys.databases from this article. Both DMVs have database_id columns, which we can use to join them. We can join the sys.databases and sys.master_files dynamic management views. The example of sp_databases is here: USE master Joining sys.databases and sys.master files.We can populate the list of all databases with their size by querying any of the following: Now, let us see how we can populate the list of databases with their size. SELECT HTML formatted output looks like the following image: ('master','msdb','tempdb','model') FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+'' The SQL Code to generate the HTML table is following: DECLARE nvarchar(max)įROM sys.master_files masterfiles WHERE DB_NAME(database_id) You can read this article to learn more about the sys.master_files. We can use sys.master_files dynamic management view to show the details of the database file types, their size, growth, and physical location. The HTML webpage looks like the following image: Td = CONVERT(VARCHAR,CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100),' 'ĬROSS APPLY sys.dm_os_volume_stats(masterfiles.database_id, masterfiles.FILE_ID)volume FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+'' The HTML code to create a table with disk information is following: DECLARE nvarchar(max) You can read more about it in this article. In this article, we use sys.dm_os_volume_stats to populate the storage details on the server. Using this PowerShell will return the following information: The commands are the following: Get-WmiObject -Class win32_logicaldisk | Format-Table DeviceId, MediaType, Space" e= To populate the details, run the following query statement in SQL Server management studio or dbForge Studio for SQL Server: SELECT DISTINCTĬONVERT(Numeric(10,2),total_bytes/1048576/1024) AS ,Ĭonvert(Numeric(10,2),available_bytes/1048576/1024) AS ,ĬAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS ĬROSS APPLY sys.dm_os_volume_stats(database_id, file_id)Īlternatively, you can use Get-WmiObject or commandlet to populate the disk information of the local computer. We can also use a dynamic management function, named sys.dm_os_volume_stats, to populate the disk space. This procedure only provides the available space on the server for each drive. We can use the xp_fixeddrives stored procedure to populate the details of the server's disk drives. Get a list of drives, size of disk, and percentage available space Moreover, we will create an HTML-formatted email that contains details of the above items. List of the database with other configuration parameters.List of drives, size of disk, and percentage available space. The T-SQL script contains queries to populate the following details. $DataSet.This article explains the T-SQL scripts that can be used to manage and monitor the disk space on the database server. #Specify the output location of your dump file # Loop through all tables and export a CSV of the Table Dataįoreach ($Row in $) $connection.ConnectionString = $connectionString $connectionString = ::Format($connectionTemplate, $server, $database) $tablequery = "SELECT s.name as schemaName, t.name as tableName from sys.tables t inner join sys.schemas s ON t.schema_id = s.schema_id" I used the powershell script to export all my tables into csv. The export wizard allows only one at a time.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |