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