SQL Tips

1) Below query is used to return list of procedure with created and modified date.

SELECT name,create_date,modify_date

FROM SYS.OBJECTS

WHERE type = 'P'

ORDER BY create_date DESC

2) Below query is used to return list of User Table with created and modified date.

SELECT name,create_date,modify_date

FROM SYS.OBJECTS

WHERE type = 'U'

ORDER BY create_date DESC

3) Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime

SELECT

CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,

CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly

4) Get user table of selected database.

SELECT name

FROM SYS.OBJECTS

WHERE type = 'U'

ORDER BY name

5) Get table all filed with data types and size

select

column_name + ' (' + data_type + case isnull(character_maximum_length,'') when '' then '' else '(' + cast (character_maximum_length AS varchar(6)) + ')' end + case is_nullable when 'YES' then ', null)' else ', not null)' end AS Columns

from information_schema.columns

where table_name = 'projects'

order by ordinal_position

6) Get Procedure/Function Parameter details.

SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema],

SO.name AS [ObjectName],

SO.Type_Desc AS [ObjectType (UDF/SP)],

P.parameter_id AS [ParameterID],

P.name AS [ParameterName],

TYPE_NAME(P.user_type_id) AS [ParameterDataType],

P.max_length AS [ParameterMaxBytes],

P.is_output AS [IsOutPutParameter]

FROM sys.objects AS SO

INNER JOIN sys.parameters AS P

ON SO.OBJECT_ID = P.OBJECT_ID

WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID

FROM sys.objects

WHERE TYPE IN ('P','FN'))

ORDER BY [Schema], SO.name, P.parameter_id

7) Select data where row number “Between” 50 to 60.

WITH OrderedOrders AS

(

SELECT [User_ID], [User_Name], Created_Date,

ROW_NUMBER() OVER (order by Created_Date) AS 'RowNumber'

FROM Mst_User

)

SELECT *

FROM OrderedOrders

WHERE RowNumber between 50 and 60;

8) Rank()

SELECT [User_ID], [User_Name], Created_Date,

RANK() OVER (order by Created_Date) AS 'RowNumber'

FROM Mst_User

9) Row_Number()

SELECT [User_ID], [User_Name], Created_Date,

ROW_NUMBER() OVER (order by Created_Date) AS 'RowNumber'

FROM Mst_User

10)Select Row_Number() and group wise Role_ID

SELECT ROW_NUMBER() OVER (

PARTITION BY Role_ID

ORDER BY Created_Date) AS 'RowNumber',

[User_ID], *

FROM mst_user

11)query to show read\write ratio of the instance since the last restart

SELECT db_name(), sum(user_seeks + user_scans + user_lookups) as Reads , sum(user_updates) as 'insert, update, or deletes'

FROM sys.dm_db_index_usage_stats

12)The script below provides examples of the OBJECTPROPERTY system meta data function.

DECLARE @ObjectID INT

SET @ObjectID = OBJECT_ID('testRachit')

SELECT 'IsTable' AS [ObjectProperty], OBJECTPROPERTY(@ObjectID, 'IsTable') AS [Value]

UNION ALL

SELECT 'IsVIew', OBJECTPROPERTY(@ObjectID, 'IsVIew')

UNION ALL

SELECT 'IsProcedure', OBJECTPROPERTY(@ObjectID, 'IsProcedure')

UNION ALL

SELECT 'IsScalarFunction', OBJECTPROPERTY(@ObjectID, 'IsScalarFunction')

UNION ALL

SELECT 'IsTableFunction', OBJECTPROPERTY(@ObjectID, 'IsTableFunction')

UNION ALL

SELECT 'IsExecuted', OBJECTPROPERTY(@ObjectID, 'IsExecuted')

UNION ALL

SELECT 'TableHasIndex', OBJECTPROPERTY(@ObjectID, 'TableHasIndex')

UNION ALL

SELECT 'TableHasClustIndex', OBJECTPROPERTY(@ObjectID, 'TableHasClustIndex')

UNION ALL

SELECT 'TableHasPrimaryKey', OBJECTPROPERTY(@ObjectID, 'TableHasPrimaryKey')

UNION ALL

SELECT 'TableHasIdentity', OBJECTPROPERTY(@ObjectID, 'TableHasIdentity')

UNION ALL

SELECT 'HasAfterTrigger', OBJECTPROPERTY(@ObjectID, 'HasAfterTrigger')

UNION ALL

SELECT 'HasInsteadOfTrigger', OBJECTPROPERTY(@ObjectID, 'HasInsteadOfTrigger')

GO

OUTPUT

IsTable 1

IsVIew 0

IsProcedure 0

IsScalarFunction 0

IsTableFunction 0

IsExecuted 0

TableHasIndex 1

TableHasClustIndex 1

TableHasPrimaryKey 1

TableHasIdentity 1

HasAfterTrigger 0

HasInsteadOfTrigger 0

13)The script below provides examples on how you can use the INDEXPROPERTY system meta data function with different properties.

DECLARE @ObjectID INT

SET @ObjectID = OBJECT_ID('testRachit')

SELECT 'IsClustered' AS [IndexProperty], INDEXPROPERTY(@ObjectID, 'testRachit_ID', 'IsClustered') AS [Value]

UNION ALL

SELECT 'IsUnique', INDEXPROPERTY(@ObjectID, 'testRachit_ID', 'IsUnique')

UNION ALL

SELECT 'IsDisabled', INDEXPROPERTY(@ObjectID, 'testRachit_ID', 'IsDisabled')

UNION ALL

SELECT 'IndexFillFactor', INDEXPROPERTY(@ObjectID, 'testRachit_ID', 'IndexFillFactor')

UNION ALL

SELECT 'IndexDepth', INDEXPROPERTY(@ObjectID, 'testRachit_ID', 'IndexDepth')

GO

OUTPUT :

IsClustered NULL

IsUnique NULL

IsDisabled NULL

IndexFillFactor NULL

IndexDepth NULL

14)The script below demonstrates how you can use the COLUMNPROPERTY system meta data function with different properties.

DECLARE @ObjectID INT

SET @ObjectID = OBJECT_ID('testRachit')

SELECT 'IsIdentity' AS [ColumnProperty], COLUMNPROPERTY(@ObjectID, 'testRachit_ID', 'IsIdentity') AS [Value]

UNION ALL

SELECT 'AllowsNull', COLUMNPROPERTY(@ObjectID, 'testRachit_ID', 'AllowsNull')

UNION ALL

SELECT 'IsComputed', COLUMNPROPERTY(@ObjectID, 'testRachit_ID', 'IsComputed')

UNION ALL

SELECT 'IsIndexable', COLUMNPROPERTY(@ObjectID, 'testRachit_ID', 'IsIndexable')

UNION ALL

--Sparse column feature is introduced in SQL Server 2008

SELECT 'IsSparse', COLUMNPROPERTY(@ObjectID, 'testRachit_ID', 'IsSparse')

GO

OUTPUT :

IsIdentity 1

AllowsNull 0

IsComputed 0

IsIndexable 1

IsSparse NULL

15)Get File size of all database in sql 2005/2008

SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME] ,

CAST(( CAST(SIZE * 8 AS FLOAT) ) / 1024 AS VARCHAR) + ' MB' AS [SIZE]

FROM SYS.MASTER_FILES

16)Get Reandom record from table

SELECT TOP 1 pk_ThemeId FROM Theme ORDER BY NEWID()