DBA

Remove Trim last character of string in the column – SP_helpdb

Advertisements

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

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: