Tips & Tricks

Selecting Nth Row from a table

create procedure SP_FETCH_ROWS(@num int)
AS
BEGIN
select * from employee a where @num=(select count(*) from employee b where a.id >= b.id)
END 

exec SP_FETCH_ROWS 3

 

 

Selecting Nth largest value from a Row in a table

SELECT max(id) FROM contentmaster WHERE id < (SELECT max(id) FROM contentmaster WHERE id < (SELECT max(id) FROM contentmaster))

 

 

Getting Autogenerated Sno

ROW_NUMBER() OVER (ORDER BY b.date asc) As SNo

 

 

Converting Time Format from 24hr to 12hr

select Right(convert(varchar,timein,100),7) from timesheetvendordetail

 

 

 Renaming a column in Sql Server

We often need to change the name of a column of a table to a new name. We  can do this with the help of the Exec Sp_rename command.

 The Syntax of the Sp_rename is given below:-

 Exec sp_rename ‘TableName.[OldColumnName]’, ‘[NewColumnName]’, ‘Column’

 For example, suppose we have a table called Employee who has the following structure:-
 CREATE TABLE Employee (Emplyeeid int identity(1,1),
                                      Empnumber nvarchar(10),
                                      Firstname nvarchar(150), 
                                      Lastname nvarchar(150),
                                      Age int, 
                                      Phoneno nvarchar(15), 
                                      Address nvarchar(200),
                                      Empdate datetime)

Now suppose we insert the following data into the table Employee
Insert into Employee values(‘abc123’, ‘Abc’, ‘Xyz’, 25, ‘123456’, ‘Delhi’, getdate())

Suppose now  we want to change the column name Firstname to Fname the we use the given query:-
Exec Sp_rename ‘Employee.Firstname’, ‘Fname’, ‘column’

Here the column name “Firstname” changed to the column “Fnamn”.

 

 

Getting Column names from table

SELECT tb.TABLE_NAME,
tc.COLUMN_NAME,
tc.DATA_TYPE,
tc.CHARACTER_MAXIMUM_LENGTH,
tc.NUMERIC_PRECISION,
tc.IS_NULLABLE, tb.TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES tb INNER JOIN
INFORMATION_SCHEMA.COLUMNS tc ON
(tb.TABLE_CATALOG = tc.TABLE_CATALOG) AND
(tb.TABLE_NAME    = tc.TABLE_NAME   ) AND
(tb.TABLE_SCHEMA  = tc.TABLE_SCHEMA )
WHERE tb.TABLE_TYPE = ‘BASE TABLE’ and tb.TABLE_NAME=’EmpEmploymentHistory’
 

 

Getting Column names from Views

SELECT tb.TABLE_NAME,
tc.COLUMN_NAME,
tc.DATA_TYPE,
tc.CHARACTER_MAXIMUM_LENGTH,
tc.NUMERIC_PRECISION,
tc.IS_NULLABLE, tb.TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES tb INNER JOIN
INFORMATION_SCHEMA.COLUMNS tc ON
(tb.TABLE_CATALOG = tc.TABLE_CATALOG) AND
(tb.TABLE_NAME    = tc.TABLE_NAME   ) AND
(tb.TABLE_SCHEMA  = tc.TABLE_SCHEMA )
WHERE tb.TABLE_TYPE = ‘VIEW’

 

Getting Column names from table valued Functions
SELECT tb.ROUTINE_NAME,
tc.COLUMN_NAME,
tc.DATA_TYPE,
tc.CHARACTER_MAXIMUM_LENGTH,
tc.NUMERIC_PRECISION,
tc.IS_NULLABLE
FROM INFORMATION_SCHEMA.ROUTINES tb INNER JOIN
INFORMATION_SCHEMA.ROUTINE_COLUMNS tc ON
(tb.ROUTINE_CATALOG = tc.TABLE_CATALOG) AND
(tb.ROUTINE_NAME    = tc.TABLE_NAME  ) AND
(tb.ROUTINE_SCHEMA  = tc.TABLE_SCHEMA )

 

 

 

%d bloggers like this: