sql server disk space usage by database

sql server disk space used by database.
You need to run following query on each database to get information by tables and its size on disk with number of rows.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    SCHEMANAME,TotalSpaceMB DESC, t.Name

Microsoft sql server MDS

Documents and links related to implement Microsoft MDS

Checklists/Points to be aware off-
Make sure the version of DB and MDS are same.
You can download excel plugin from MDS web url.
Make sure all the required IIS programs are added.
Package file cannot be deployed using MDS-UI if the package has data.
To deploy package with data, use MDSModelDeploy.exe tool (accessed via command line utility)
To deploy package without data you could use create/deploy option at MDS-web UI.

This is a good course to get an idea –
https://www.udemy.com/sql-server-master-data-services-master-data-management/learn/v4/questions/6408682

Master Data Services Installation and Configuration
https://docs.microsoft.com/en-us/sql/master-data-services/master-data-services-installation-and-configuration?view=sql-server-2017#InstallMDS

Uninstall and Remove Master Data Services
https://docs.microsoft.com/en-us/sql/sql-server/install/uninstall-an-existing-instance-of-sql-server-setup?view=sql-server-2017

Master Data Services Developer Documentation
https://docs.microsoft.com/en-us/sql/master-data-services/develop/master-data-services-developer-documentation?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/tutorials/enterprise-information-management-using-ssis-mds-and-dqs-together-%5Btutorial%5D?view=sql-server-2014

https://docs.microsoft.com/en-us/sql/master-data-services/master-data-services-overview-mds?view=sql-server-2017

 

 

 

float to nvarchar in sql server

when you need to select from one column of datatype float(which has phone number) and insert it in another column as nvarchar- cast wont work

cast : cast([Column_Name] as nvarchar(50)). The result will be in format 9.07235e+009 instead of a 10 digit number (phone number).

use SELECT STR([Column_Name],10,0) ** This is SQL Server solution, for other servers check their docs