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()