Microsoft SQL Server - Important Queries

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'

2 comments:

manpreet said...

Thanks for your code snippet . It was really helpful in solving my problem.

Unknown said...

very good stuff...