sanwar_mal_jat - Professional Profile

exec sp_msforeachtable @command1="print '?'" -- Print all table name in a database

exec sp_msforeachtable N'SELECT [?] = count(*) FROM Movies.? ' -- Count how many ROW in table of a databse all tables

SELECT 'USE [' + db_name() +']';-- Check using database name 
 
SELECT 'ALTER TABLE ' +                          -- Remove foreigh key constaint of alll tables in using databse
       '[' + sche.name + '].[' +tbl.name + ']' +
       ' DROP CONSTRAINT [' + fK.name + ']'
  FROM sys.foreign_keys fK
 INNER JOIN sys.TABLES tbl ON fK.parent_object_id=tbl.object_id
 INNER JOIN sys.schemas sche ON tbl.schema_id=sche.schema_id
 WHERE tbl.is_ms_shipped=0;
 
 
SELECT 'TRUNCATE TABLE ' + '[' + sche.name + '].[' + tbl.name + ']'    -- Truncate all table in a Using database   
  FROM sys.TABLES tbl
 INNER JOIN sys.schemas sche ON tbl.schema_id=sche.schema_id
 WHERE tbl.is_ms_shipped=0;
 
 
SELECT 'ALTER TABLE ' +                                            -- Recreate foreign key containt all table in a using database   
       '[' + sche.name + '].[' + tbl.name + ']' +
       ' ADD CONSTRAINT [' + f.name + ']' +
       ' FOREIGN KEY (' +        
       Stuff( (SELECT ', ['+col_name(fk.parent_object_id, fk.parent_column_id) +']'
                 FROM sys.foreign_key_columns fk
                WHERE constraint_object_id = f.object_id
                ORDER BY constraint_column_id
                  FOR XML Path('')
            ), 1,2,'') + ')' +
       ' REFERENCES [' +
       object_schema_name(f.referenced_object_id)+'].['+object_name(f.referenced_object_id) + '] (' +
       Stuff((SELECT ', ['+col_name(fc.referenced_object_id, fc.referenced_column_id)+']'
                FROM sys.foreign_key_columns fc
               WHERE constraint_object_id = f.object_id
               ORDER BY constraint_column_id
                 FOR XML Path('')),
              1,2,'') +
        ')' +
        ' ON DELETE ' + REPLACE(f.delete_referential_action_desc, '_', ' ')  +
        ' ON UPDATE ' + REPLACE(f.update_referential_action_desc , '_', ' ') collate database_default
  FROM sys.foreign_keys f
 INNER JOIN sys.TABLES tbl ON f.parent_object_id=tbl.object_id
 INNER JOIN sys.schemas sche ON tbl.schema_id=sche.schema_id
 WHERE tbl.is_ms_shipped=0;

private string  DecimaltoBinary(int Number)
{


   string  res1 = "999";
   int args = Number;
    while (args > 1)
    {
       int arg1 = Convert.ToInt32(args / 2);
       int arg2 = args % 2;
        args = arg1;
        if (res1 == "999")
        {
            res1 = arg2.ToString();
        }
        else
        {
            res1 = arg2.ToString() + res1.ToString();
        }
    }
    if (args == 1 && res1 != "999")
    {
        res1 = args.ToString() + res1.ToString();
    }
    else if (args == 0 && res1 == "999")
    {
        res1 = "0";
    }
    else if (res1 == "999")
    {
        res1 = "1";
    }

    int ll = res1.Length;
    while (ll % 4 != 0)
    {
        res1 = "0" + res1;
        ll = res1.Length;
    }

    return res1;
}

<a href='mailto:To@domain.com,AnotherTo@domain.com?CC=cc@domain.com,Anothercc@domain.com&BCC=Bcc@domain.com,AnotherBcc@domain.com&Subject=enter your subject;&Body=enter mail message'>click hear to send a auto fill address and body of a mail</a> 

using System.Security.Cryptography;
using System.Text;
using System.IO;   

 private const string ENCRYPTION_KEY = "key";
    private readonly static byte[] SALT = Encoding.ASCII.GetBytes(ENCRYPTION_KEY.Length.ToString());

    public static string Encrypt(string inputText)
    {
        RijndaelManaged rijndaelCipher = new RijndaelManaged();
        byte[] plainText = Encoding.Unicode.GetBytes(inputText);
        PasswordDeriveBytes SecretKey = new PasswordDeriveBytes(ENCRYPTION_KEY, SALT);

        using (ICryptoTransform encryptor = rijndaelCipher.CreateEncryptor(SecretKey.GetBytes(32), SecretKey.GetBytes(16)))
        {
            using (MemoryStream memoryStream = new MemoryStream())
            {
                using (CryptoStream cryptoStream = new CryptoStream(memoryStream, encryptor, CryptoStreamMode.Write))
                {
                    cryptoStream.Write(plainText, 0, plainText.Length);
                    cryptoStream.FlushFinalBlock();
                    return Convert.ToBase64String(memoryStream.ToArray());
                }
            }
        }
    }

    public static string Decrypt(string inputText)
    {
        RijndaelManaged rijndaelCipher = new RijndaelManaged();
        byte[] encryptedData = Convert.FromBase64String(inputText);
        PasswordDeriveBytes secretKey = new PasswordDeriveBytes(ENCRYPTION_KEY, SALT);

        using (ICryptoTransform decryptor = rijndaelCipher.CreateDecryptor(secretKey.GetBytes(32), secretKey.GetBytes(16)))
        {
            using (MemoryStream memoryStream = new MemoryStream(encryptedData))
            {
                using (CryptoStream cryptoStream = new CryptoStream(memoryStream, decryptor, CryptoStreamMode.Read))
                {
                    byte[] plainText = new byte[encryptedData.Length];
                    int decryptedCount = cryptoStream.Read(plainText, 0, plainText.Length);
                    return Encoding.Unicode.GetString(plainText, 0, decryptedCount);
                }
            }
        }
    }

Select '130'+'120'+1 --> Ans-130121  	In this query the first plus sign between two strings indicates the concatenation and the second plus sign between second string and digit 1 indicates the addition the string will converted to digit at the time of addition. 

SELECT DATEADD(s,0,0) --> Ans- 1900-01-01 00:00:00.000  DATEADD function adds zero seconds to default datetime 1900-01-01 00:00:00.000 

SELECT ISNULL(1,2) -->  Ans 1	ISNULL checks the first argument, if it is NULL then it returns the second argument. Else it returns first argument. 

-- Ques Table A has 10 rows including 3 Null rows. Table B has 7 rows including 3 Null rows. 
Select Count(*) from [Table A]Select Count(Column Name) from [Table B]
--> Ans- Table A will return 10 rows and Table B will return 4 rows   (Because when we use count(*),it will return total number of rows in a table including null rows. But count(column Name) will only return NOT NULL rows.)


-- Ques-- What is the maximum number of columns that can participate in a TABLE's primary key? --> 16  

 -- Quest  	The default varchar, nvarchar datatype length is 30  NOTE->when we use it with the cast option ,Using as like
 select  CAST('aaaaaaaaaaaaaaaaaaaaabbddddddddddddFF' as nvarchar) 
 select CONVERT(nvarchar ,'aaaaaaaaaaaaaaaaaaaaabbddddddddddddFF')
 
 -- if  use this type then it will take only Default strating 1 value
 
 declare @Type nvarchar
set @Type='aaaaaaaaaaaaaaaaaaaaabbddddddddddddFF'  
print @Type  
 
 
 -- In SQL Server 2012, how many index types are available?  Answer-->10,12
 
 -- 1. A Temporary Table can have primary keys and indexes defined -Answer-->True

--2. A Table Variable can have primary keys and indexes defined -Answer-->True


--How many maximum processor cores does SQL Server 2008 R2 Datacenter edition support?  -Answer-->256

Select * from (  SELECT   ROW_NUMBER() OVER (
 ORDER BY ProductID) AS  RowNumber ,ProductID  ,
        Product,Title,Colour,Fabric,Weight,
        YourPrice,SalePrice,IsDeleted ,IsUploaded,IsUpdated
         FROM RSS_tblProductsMaster)xx   inner join
         ( select MAX( aa.RowNumber)as  RowNumber from  (
         SELECT   ROW_NUMBER() OVER (
         ORDER BY ProductID) AS  RowNumber ,ProductID
         FROM RSS_tblProductsMaster )
         aa group by  aa.ProductID)  YY on xx.RowNumber=YY.RowNumber

IF OBJECT_ID('tempdb..#RSS_tblProductsMaster') IS NOT NULL                                              
BEGIN                                              
drop table #RSS_tblProductsMaster                                            
END 
SELECT   ROW_NUMBER() OVER (
ORDER BY ProductID) AS 'RowNumber',ProductID  , 
         Product,Title,Colour,Fabric,Weight,               
                   YourPrice,SalePrice,IsDeleted ,IsUploaded,IsUpdated       
             into #RSS_tblProductsMaster FROM RSS_tblProductsMaster
             
delete
FROM #RSS_tblProductsMaster
WHERE  RowNumber NOT IN
(
SELECT MAX(RowNumber)
FROM #RSS_tblProductsMaster
GROUP BY ProductID)

select * from #RSS_tblProductsMaster

  My table is 

      Col1 Col2 Col3
      1     2     3
      1     4     5
      2     5     6

Require RESULT of This table 

     Col1 Col2 Col3
     1      2     3
     2      5     6

select * from duplicate
truncate table duplicate2
--select * into duplicate2 from duplicate where 1=2



    declare @col1 int=0,@col2 int=0,@col3 int=0
    declare mycur cursor for select col1 from duplicate
    OPEN mycur
    FETCH NEXT FROM mycur INTO @col1

    WHILE @@FETCH_STATUS = 0
        BEGIN
                if not exists (select 1 from duplicate2 where col1=@col1)
                    Begin
                        select @col2=col2,@col3=col3 from duplicate  where col1=@col1
                        insert into duplicate2(col1,col2,col3) values(@col1,@col2,@col3)
                    End
            FETCH NEXT FROM mycur INTO @col1
        end

    close mycur
    deallocate mycur

     select * from duplicate2

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Entertablename%'

object objec = null;
string objValue1 = objec .ToString(); //It is does not handle Null Value and throws Null reference exception
string objValue2 = Convert.ToString(objec); //It is  handle Null Value and  output Null

DECLARE @position int, @string varchar(100), @FirstStrVal int
DECLARE @position1 int, @string1 varchar(100), @SecondStrVal int
 set @FirstStrVal=0
  set @SecondStrVal=0
SET @position = 1
SET @position1 = 1
SET @string = rtrim(ltrim ('AAAAAA'))
SET @string1 = rtrim(ltrim ('BBBBBB'))

WHILE @position <= DATALENGTH(@string)
   BEGIN
   set @FirstStrVal=@FirstStrVal+( SELECT  ASCII(SUBSTRING(@string, @position, 1)))
   SET @position = @position + 1

 END



 WHILE @position1 <= DATALENGTH(@string1)
   BEGIN
   set @SecondStrVal=@SecondStrVal+(  SELECT  ASCII(SUBSTRING(@string1, @position1, 1)))
  SET @position1 = @position1 + 1

 END



 if(@SecondStrVal>@FirstStrVal)
 begin
 print 'Second String > First String'
 end
 else
 begin
 print  'First String > Second String'
 end

declare @house  nvarchar(200)
declare @city  nvarchar(200)
declare @state nvarchar(200)


set @house='50';
set @city='Himaat nagar';
set @state='Delhi';

select case when @house !='' then  @house else '' end  +  (case when @city !='' then case when @house!='' then ' , '+@city else @city end else ''  end)  +

 (case when @state !='' then case when @city!='' then ' , '+@state else @state end else ''end)