Sreenivasa Reddy Thotli

  go /* Description:- This Query will provide you detail of missing indexes on a table and also prepare sql script for new index */   SELECT DISTINCT @@ SERVERNAME AS [ ServerName ] , DB_NAME () AS [ DatabaseName ] , SCHEMA_NAME ([ systemObject ].[ schema_id ]) AS [ SchemaName ] ,[ systemObject ]. Object_ID AS ObjectID , [ systemObject ].[ name ] AS [ ObjectName ] , CASE [ systemObject ].[ type ] WHEN 'U' THEN 'Table' WHEN 'V' THEN 'View' ELSE 'Unknown' END AS [ ObjectType ] , [ MissingIndexDetail ].[ equality_columns ] AS [ EqualityColumns ] , [ MissingIndexDetail ].[ inequality_columns ] AS [ InequalityColumns ] , [ MissingIndexDetail ].[ included_columns ] AS [ IncludedColumns ] , [ MissingIndexGroupState ].[ user_seeks ] AS [ ExpectedIndexSeeksByUserQueries ] , [ MissingIndexGroupState ].[ user_scans ] AS [ ExpectedIndexScansByUserQueri

  CREATE FUNCTION [DBO].[FX_REMOVEBRACES] (@TEXT NVARCHAR(MAX))     RETURNS NVARCHAR(MAX) AS BEGIN     DECLARE @TAG_START  INT     DECLARE @TAG_END    INT     DECLARE @TAG_LENGTH INT     SET @TAG_START = CHARINDEX('(', @TEXT)     SET @TAG_END = CHARINDEX(')', @TEXT, CHARINDEX('(', @TEXT))     SET @TAG_LENGTH = (@TAG_END - @TAG_START) + 1     WHILE @TAG_START > 0 AND @TAG_END > 0 AND @TAG_LENGTH > 0         BEGIN             SET @TEXT = STUFF(@TEXT,@TAG_START,@TAG_LENGTH, '')             SET @TAG_START = CHARINDEX('(',@TEXT)             SET @TAG_END = CHARINDEX(')',@TEXT,CHARINDEX('(', @TEXT))             SET @TAG_LENGTH = (@TAG_END - @TAG_START) + 1         END     RETURN LTRIM(RTRIM(@TEXT)) END Input : SELECT dbo.[fx_removebraces]('Metals Ltd(72.39%), Uranium Ltd(20.82%), Exploration NL(6.79%)') Output : Energy Metals Ltd, Elevate Uranium Ltd, Southern Cross Exploration NL

DECLARE @StartDate DATE = '2019-12-17'; DECLARE @EndDate DATE = '2021-05-01' declare @Edate date declare @sdate date set @Edate = DATEADD(day, 30, @StartDate) set @sdate = @StartDate WHILE (@Edate <= @EndDate) BEGIN print 'start' print @sdate print @Edate set @sdate = @Edate set @Edate = DATEADD(day, 30, @sdate); print @sdate print @Edate print 'end' END

SELECT DEV.name as DEV_ColumnName, PROD.name as PROD_ColumnName, DEV.is_nullable as DEV_is_nullable, PROD.is_nullable as PROD_is_nullable, DEV.system_type_name as DEV_Datatype, PROD.system_type_name as PROD_Datatype, DEV.is_identity_column as DEV_is_identity, PROD.is_identity_column as PROD_is_identity   FROM sys.dm_exec_describe_first_result_set (N'SELECT * FROM [Database1].dbo.Employee', NULL, 0) DEV FULL OUTER JOIN  sys.dm_exec_describe_first_result_set (N'SELECT * FROM [Database2].dbo.Employee', NULL, 0) PROD ON DEV.name = PROD.name  

 DECLARE @SearchStr nvarchar(100) SET @SearchStr = 'Boarding' -- Example for string Boarding CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON   DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET  @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')   WHILE @TableName IS NOT NULL   BEGIN     SET @ColumnName = ''     SET @TableName =     (         SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))         FROM     INFORMATION_SCHEMA.TABLES         WHERE         TABLE_TYPE = 'BASE TABLE'             AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName             AND    OBJECTPROPERTY(                     OBJECT_ID(                         QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)                          ), 'IsMSShipped'                      

 In SQL server by using function NT ILE we can split the equal number of records from a table to number of groups SELECT *,NTILE(5) over( ORDER by Id) GroupNo from Table Here records will be assigned to 5 groups (Group No: 1,2,3,4,5).