Does prefixing system or general table names with "Sys" fine in Sql Server?-CodePudding

In Sql Server there's sys Schema. sys.Columns, sys.Tables. I have a general tables which I need to rename to SysUsers, SysRoles, etc.

Is it fine to do that? I will not use Sys schema. I just will prefix tables with Sys I find SQL Server itself do it generally

select * FROM SysColumns      // NOTE its not sys.Columns

CodePudding user response：

TL;DR: Don't do this.

You will have name clashes and your objects will be unusable.

I strongly recommend against this. If you have any objects where the names clashes you will not be able to reference it.

Take this simple example:

USE master;
GO

CREATE DATABASE Sillytest;
GO

USE Sillytest;
GO

SELECT *
FROM syscolumns; 
GO

SELECT *
FROM dbo.syscolumns; 
GO
SELECT *
FROM sys.syscolumns; 

GO

CREATE TABLE dbo.syscolumns (ID int, ColumnName sysname);
GO

SELECT *
FROM syscolumns; 
GO

SELECT *
FROM dbo.syscolumns; 
GO
SELECT *
FROM sys.syscolumns; 
GO

USE master;
GO

DROP DATABASE Sillytest;
GO

Every single reference to syscolumns, whether prefixed by dbo, sys, or not at all, references the object sys.syscolumns. Not one of those statements returns data from the (empty) user table I created dbo.syscolumns. Notice, as well, that the reference to dbo.syscolumns before I create a table of that name also works.

Yes, you can create the objects, but if the name already exists as a sys.sys{object} object, then you won't be able to use it.

Further to this, there is already an object sys.sysusers, so we actually have the answer; don't do it, you already have a clash.

CodePudding user response：

Total agreement with @Larnu and I upvoted that answer. Let me add one more.

By putting a single prefix at the front of all your table names, you actually hurt your ability to search, sort, and filter, your tables. It will slow down the database. It will hurt SSMS behavior and 3rd party tools.

Don't do this, but also, don't do it with something else either; tbl, tab, none of that junk.