Posts

Easy way to Compare two SQL Server table schemas

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  

Search in all tables and columns for a specific value in SQL Server

 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...

Split rows into equal number of records per group in sql server

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

Get Random alphanumeric number with specific length using SQL Server

Create a function to get the new guid create view [dbo].[MyNewGuid] as select newid() as NewID; Call below user defined function and pass the number of characters to be returned. CREATE FUNCTION [dbo].[ufn_RandomString](     @pStringLength int = 10 -- No of characters to be returned     ,@isupper bit=0 -- will be returned in upper case or lowercase ) returns varchar(max) as begin     declare  @RandomStr varchar(max);     with     a1 as (select 1 as N union all            select 1 union all            select 1 union all            select 1 union all            select 1 union all            select 1 union all         ...

Finding nearby longitude and latitude within specific distance in database

 To get near by cities which are having longitude and latitude within specific distance in database SELECT * FROM ( SELECT * FROM ( SELECT *,(((acos(sin((@latitude*pi()/180)) * sin((Latitude*pi()/180))+cos((@latitude*pi()/180))  * cos((Latitude*pi()/180)) * cos(((@longitude - Longitude)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance  FROM tblCities ) t )x where distance <= 1000 ORDER by distance

List of special Characters in HTML

List of special Characters in HTML left single quote   &lsquo; ‘ right single quote   &rsquo; ’ single low-9 quote   &sbquo; ‚ left double quote   &ldquo; “ right double quote   &rdquo; ” double low-9 quote   &bdquo; „ dagger   &dagger; † double dagger   &Dagger; ‡ per mill sign   &permil; ‰ single left-pointing angle quote   &lsaquo; ‹ single right-pointing angle quote   &rsaquo; › black spade suit   &spades; ♠ black club suit   &clubs; ♣ black heart suit   &hearts; ♥ black diamond suit   &diams; ♦ overline, = spacing overscore   &oline; ‾ leftward arrow   &larr; ← upward arrow   &uarr; ↑ rightward arrow   &rarr; → downward arrow   &darr; ↓ trademark sign &#x2122; &trade; ™ unused &#00;- &#08;     horizontal tab &#09;     line feed &#10; ...