Sql-server – How to remove dead SQL Server registrations from SQL Server 2000 Enterprise Manager

Thought of using virtualisation?

This isn't due to the fact that the names actually have spaces, it's due to the fact that Sql Server stores this data in a field of the type nchar(128), which is a fixed-width character data type (i.e. the data stored will always be 128 characters long, padded with spaces if necessary). So, when you query for that value, you'll always get a type that is 128 characters in length. If you want to trim the trailing space padding of the datatype, simply cast it to a variable-length datatype, or explicitly trim it, like this:

select cast(name as nvarchar(128)) as name from sysfiles

or

select rtrim(name) as name from sysfiles

or if you are planning to insert to another table, it will implicitly cast if the target datatype is variable length and compatible:

create table #files (name nvarchar(128));
insert #files (name) select name from sysfiles;
select name from #files;

If you slightly modify your statement above, you should notice that there are no trailing X's any longer:

select replace(cast(name as nvarchar(128)), ' ', 'X') from sysfiles