Remove Trim last character of string in the column – SP_helpdb

The requirement is load sp_helpdb for all databases in the server, about 100+ serves to check database size quickly (sp_helpdb will hold both used and unused).

Use CMS registered server, copy the data and paste in excel, import the excel file into a table. (OR) use BCP command.

sp_helpdb

create table tbl_sp_helpdb (
server_name varchar (100),name varchar(100),Size_in_mb varchar(100),owner varchar(100),
dbid int,created datetime,  status  varchar(100), compatibility_level int
)
-- Import the excel
select * from tbl_sp_helpdb order by Size_in_mb

alter table tbl_sp_helpdb alter column Size_in_mb numeric
--Error converting data type nvarchar to numeric.

select left (Size_in_mb,LEN(Size_in_mb) -2) as Size_in_mb,* from tbl_sp_helpdb order by Size_in_mb



update tbl_sp_helpdb set Size_in_mb =left (Size_in_mb,LEN(Size_in_mb) -2)

alter table tbl_sp_helpdb alter column Size_in_mb numeric

select * from tbl_sp_helpdb order by Size_in_mb