SQL Server Rename Table, Trigger ,View

Rename View

Sometimes ,it is necessary to rename view. There is two ways to rename a View. You can do it from object Explorer ,or you can do it by writing script. Here's the step ,how can we change the name of a view from object Explorer. 

1)Form object Explorer select database 
2)Select view, right click on the view you will get a context menu. 3)Select rename, write the name of the view . 

You can also rename a View using "sp_rename". Below is example of view rename.For example , we have created a view as name "Student_List" and renamed it "new_Student_List"

 

CREATE VIEW Student_List AS

SELECT * FROM STUDENT

EXEC sp_rename 'dbo.Student_List', 'new_Student_List'

Rename Trigger

You can rename a  Trigger. Trigger can be the name from two places one from object Explorer and other from t-sql. Here's the step ,how can we change the name of a Trigger from object Explorer.


1)Form object Explorer select database 
2)Select Trigger, right click on the Triggeryou will get a context menu.  
3)Select rename, write the name of the Trigger.

You can also rename a Trigger using "sp_rename". Below is example of Trigger rename.For example , we have created a Trigger as name "tr_student" and renamed it "new_tr_student"
 

CREATE TRIGGER tr_student

ON STUDENT

         AFTER INSERT, UPDATE, DELETE

         AS PRINT ('You made one DML operation');

GO

EXEC sp_rename 'dbo.tr_student', 'new_tr_student'

Rename Table

You can rename a  Table. Table can be the name from two places one from object Explorer and other from t-sql. Here's the step ,how can we change the name of a Table from object Explorer.


1)Form object Explorer select database 
2)Select Table, right click on the Table you will get a context menu.  
3)Select rename, write the name of the Table.

You can also rename a Table using "sp_rename". Below is example of Table rename.For example , we have created a Table as name "STUDENT" and renamed it "new_STUDENT"

CREATE TABLE STUDENT

(

[student_name] VARCHAR(500),

[class] INT,

[section] VARCHAR(5),

[marks] INT

);

EXEC sp_rename 'dbo.STUDENT', 'new_STUDENT'

SQL Server Split

 Some times we meet situation were we pass multiple value ,separeted with deliminator.We need to get the data and get the result in column wise.

 For example i am passing a parameter to a stored procedure which contain 5 student id,I need to get all id in a column for processing

 create procedure pr_school

 (

 @in_student VARCHAR(MAX)

 )

 EXEC pr_school '1,2,3,4,5'

In this situation we need to split the student record , we need a function

----------------------------------------------------------------

CREATE FUNCTION [dbo].[fn_GetSplit]

( 

    @sInputList VARCHAR(8000) -- List of delimited items 

  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items 

) RETURNS @List TABLE (item VARCHAR(8000)) 

 

BEGIN 

DECLARE @sItem VARCHAR(8000) 

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0 

 BEGIN 

 SELECT 

  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))), 

  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) 

  

 IF LEN(@sItem) > 0 

  INSERT INTO @List SELECT @sItem 

 END 

 

IF LEN(@sInputList) > 0 

 INSERT INTO @List SELECT @sInputList -- Put the last item in 

RETURN 

END

 ---------------------------------------------------------------

Example 1:

 SELECT item  FROM dbo.fn_GetSplit('India,Russia,japan',',')

 Result :

 item

 India

 Russia

 japan

 here , deliminator is comma(,)

 Example 2:

 SELECT item  FROM dbo.fn_GetSplit('India/==/Russia/==/japan','/==/')

 Result :

 item

 India

Russia

japan

here , deliminator is some character ('/==/')