![]() Now let’s try to run the query against the View:Ĭould not use view or function ‘DistinctLastNames’ because of binding errors. Ok, now let’s run SP_RENAME to change the LastName column to CustomerLast: EXECUTE SP_RENAME = 'Customers.LastName', = 'CustomerLast', = 'COLUMN' Here’s us creating the View and running a quick query against the View: Let’s create a very simple View that pulls the distinct LastName values from our Customers table: CREATE VIEW DistinctLastNames Ok, here’s the thing about renaming a column: You need to make sure other objects that reference the column are updated. This stored procedure is definitely a good one to know. This is pulled directly from the official Microsoft documentation: Here is the full list of values you can specify, which indicates all the different objects you can change by using SP_RENAME. There are several objects in SQL Server that can be renamed using this procedure.įor the other objects, you would simply specify the appropriate value. The SP_RENAME procedure can change more than just column names. Let’s do the same thing for the LName column. So let’s run our SELECT statement again to see the new name of our column: All parameter values are wrapped in single quotes.The value in our case is simply the word ‘COLUMN’.You use the one part identifier for the new name of our column.You need to use the two part identifier for the parameter.The call would look like this: EXECUTE SP_RENAME = 'Customers.FName', = 'FirstName', = 'COLUMN' So let’s change the name of the column to FirstName. It’s usually better to have a more descriptive name than a short name.Ĭheck out Robert Martin’s book Clean Code for more helpful tips about coding. When we name objects in SQL Server, we should really try to be as descriptive as we can be. Maybe that is a pretty poor name for a column. Ok, so let’s think about our column FName. ![]() Now let’s add some data: INSERT INTO Customers (FName, LName) Let’s create a very simple table called Customers: CREATE TABLE Customers Like the other parameters, notice this is also wrapped around single quotes. We’ll see in the next section how SP_RENAME can be used for other objects too, like indexes or a database name. ![]() In our case, the value for this parameter will simply be the word ‘COLUMN’. last parameter is the type of object we want to change. We wrap the value around single quotes, too. When we specify the new name, we do not use the two part identifier. is of course the new name we want to give our column. When we specify a value for this parameter, we wrap it around single quotes. The table is Sales, and the column within that table (the column we want to change) is EmplID. That means we include the name of the table too. When we specify the column name, we need to do so using the two part identifier. is the name of the column we want to change. There are only 3 parameters to this stored procedure: 1. The SP_RENAME system stored procedure is what we use to change a column name. So if you’ve been trying to change a column name via ALTER COLUMN, we need to change your paradigm. The ALTER COLUMN statement is meant to modify things like the column’s data type (from INT to BIGINT for example) or it’s nullability (from NULL to NOT NULL for example). After all, you are, in fact, altering the column if you rename it, right? ![]() You might think the ALTER COLUMN statement is what you would use to change the name of a column. Real quick, we need to talk about the ALTER COLUMN statement. It cannot be done through the ALTER COLUMN statement
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |