Microsoft SQL Server - Important Queries

Lakhangarg
Posted by Lakhangarg under Sql Server category on | Views : 8391
Query to Select n Rendom Records From a Table in the Database:
SELECT TOP 3 * FROM Tbl_WZQ_REGISTRATION ORDER By NEWID()

Null Parameter check in the query- we can add this check in the where condition
(@VariableData is NULL OR [ColumnName] = @VariableData

Get File Extension from given File Name
DECLARE @FileName VARCHAR(200)
SET @FileName='MyFile.ppt'
SELECT SUBSTRING(@FileName,(LEN(@FileName) - CHARINDEX('.', REVERSE(@FileName), 1 + 1)+2) ,LEN(@FileName)) AS Ext

OUTPUT: ppt

To Get The Column Name,DataType, And
Length of columns in a Table
select column_name, data_type, character_maximum_length from information_schema.columns
where table_name ={Table Name}

Query to Get List of Views
select * from information_schema.views

Query to Get List of Tables
select * from information_schema.Tables

Query to Get List of System Tables
select * from Sys.Objects where Type='s'

Query to Get List of User Tables
select * from Sys.Objects where Type='u'

Query to Get List of Store Procedures
select * from Sys.Objects where Type='p'

Query to Get List of Scalar Functions
select * from Sys.Objects where Type='fn'

Query to Get List of Table Valued Functions
select * from Sys.Objects where Type='tf'

Query to Get List of Primary Keys
select * from Sys.Objects where Type='PK'

Query to Get List of Unique Keys
select * from Sys.Objects where Type='uq'

Query to Get List of Forgien Keys
select * from Sys.Objects where Type='f'

Query to Get List of views
select * from Sys.Objects where Type='v'

Query to Get List of Triggers
select * from Sys.Objects where Type='tr'

Query to Get List of Internal Tables
select * from Sys.Objects where Type='it'

Comments or Responses

Login to post response