Friday, November 22, 2013

Get all column name for all the table name used in database.

select TABLE_NAME, column_name, ' is used to ' as 'Description'  from information_schema.columns
 where table_name in

 (
 select TableName from (
 SELECT ta.name as TableName
 ,SUM(pa.rows) RowCnt
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 having SUM(pa.rows) >0
 ) v
 )
 and column_name not in('clm1','clm2','clm3','clm4','clm5','clm6','clm7')-- add column which you did not want to add in list
order by TABLE_NAME

copy with header and paste in excel.

Monday, September 2, 2013

How to end / kill all transaction for sepecif database


USE master;
GO
ALTER DATABASE databaseName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE databaseName
SET MULTI_USER;
GO

Kill single user
exec sp_dboption 'DatabaseName', 'single user', 'FALSE'

Monday, June 10, 2013

how to get different rows from tableA with compare of tableB sql server


Using the new EXCEPT operator we can find out which rows in tableA do not exist in tableB.
It will only work with 2005 or latter version of SQL Server 2005.


create table tableX



(
 
column1 int,

column2 int



)
 
insert into tableX

select 1,1

union all

select 1,2

union all

select 1,3

union all

select 2,1

create table tableY



(
 
column1 int,

column2 int



)
 
insert into tableY

select 1,1

union all

select 1,3

union all

select 2,2



go
 
select *

from tableX



EXCEPT
 
select *

from tableY

The above statement is equals to following statement

select column1, column2
from   tableX
where not exists (select  *
                             from     tableY
                             where   tableX.column1 = tableY.column1
                                 and   tableX.column2 = tableY.column2)


Thanks
Mahesh kumar sharma

Thursday, June 6, 2013

IDENT_CURRENT vs TOP 1

How to get last inserted id in table

SELECT IDENT_CURRENT('clients')

select top 1 clientid from Clients order by 1 desc

Tuesday, June 4, 2013

Attaching the Script debugger to process 'iexplore.exe' on machine '' failed. A debugger is already attached on IE10 VS2010

I just upgraded my IE with IE10 and VS2010 start giving error
---------------------------
Microsoft Visual Studio
---------------------------
Attaching the Script debugger to process '[9999] iexplore.exe' on machine 'MACHINE-PC' failed. A debugger is already attached.
---------------------------
OK  
---------------------------

For this we have simple solution
Run Command prompt with admin mode and paste following command if you have 64 bit machine
otherwise just change the programFiles.


regsvr32.exe "%ProgramFiles(x86)%\Common Files\Microsoft Shared\VS7Debug\msdbg2.dll

Thanks
Mahesh

Thursday, May 30, 2013

How to Get Numeric Values from String in SQL-SERVER

we can create udf and use it any where in our command

CREATE
FUNCTION dbo.GetNumeric_Report


(@strAlphaNumeric VARCHAR(4000))

RETURNS INT
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN CONVERT(INT, ISNULL(@strAlphaNumeric,0))
END

Explanation of Above code :

Major use of PATINDEX which takes first argument as patter and return the index of first accurance of match value from expression.
                                                           PATINDEX ( '%pattern%' , expression )
And then STUFF delete the accurance match index from expression.

this way only integer value saved.
TEST:
select dbo.GetNumeric_Report('ahgf254bshgw3548bjdgahgfkjla');

Saturday, May 4, 2013

How to know horizontal or vertical scroll bar is present or not in browser

This is work for all Browsers IE9, Chrome,Mozial, Windows Safari..

function IsScrollBarPresents() {
            window.scrollTo(1, 1);
           if (window.pageYOffset != 0) {
                alert("vertical scrollbar present");
            }
            if (window.pageXOffset != 0) {
                alert("horizontal scrollbar present");
            }
            window.scrollTo(0, 0);
        }

Thanks
Mahesh

How to know the table name dependence on its column name

select
column_name,table_name,* from information_schema.columns

where column_name like '%isf%'

Tuesday, April 30, 2013

How to get open SQL connection of databases

SELECT
DB_NAME(dbid) AS DBName,

COUNT(dbid) AS NumberOfConnections,

loginame AS LoginName,

nt_domain AS NT_Domain,

nt_username AS NT_UserName,

hostname AS HostName

FROM
sys.sysprocesses

WHERE
dbid > 0

GROUP
BY dbid,

hostname,

loginame,

nt_domain,

nt_username

ORDER
BY NumberOfConnections DESC;

Wednesday, April 24, 2013

How to get values between two tags C#

How to get values between two tags C#


 private void df(string htmlTempate,  out int widthPersonal,  out int widthTimeLineLi)
      {
         widthPersonal = 0;
         widthTimeLineLi = 0;
         Regex regexPersonalInfo = new Regex("<widthpersonalstart>(.*)<widthpersonalend>");
         Regex regexTimelineLi = new Regex("<widthtimelinestart>(.*)<widthtimelinend>");
         var v = regexPersonalInfo.Match(htmlTempate);
         if (v.Groups != null && v.Groups.Count > 0) {
            string _widthPersonal = v.Groups[1].ToString().Trim();
            int.TryParse(_widthPersonal, out widthPersonal);
         }
         var v2 = regexTimelineLi.Match(htmlTempate);
         if (v2.Groups != null && v2.Groups.Count > 0) {
            string _widthTimeLineLi = v2.Groups[1].ToString().Trim();
            int.TryParse(_widthTimeLineLi, out widthTimeLineLi);
         }
      }
Where sample htmlTempate is as follows:
 " dasf afadf af adfadf af <widthpersonalstart> 800 <widthpersonalend>  afafadfa a adf af a adf af a adfa<widthtimelinestart>154<widthtimelinend> adfa f af adf adf ads";

Thanks
Mahesh

Wednesday, April 10, 2013

How to get numbes form a string

Suppose you have a string which contains the number and chars and in functionality you only need numeric values.
Than just replace characters with blank values after it you will have numbers only.
we can use RegEx in code.
 Code:string

s = "adsf242343";


int numbersOnly = 0;


Int32.TryParse(System.Text.RegularExpressions.Regex.Replace(s, "[^0-9]+", string.Empty),


out numbersOnly);


Thanks
Mahesh

Saturday, March 30, 2013

Limiting Textarea Text,

In asp.net max length is not working when text mode is set to multi line.
To achieve same functionality for text area , we can call same function two event
onkeyup =" return CheckMaxLenghtOfMessage(this,160)" onchange =" return CheckMaxLenghtOfMessage(this,160)"

OnKeyup will come in light when user typing text by keybord.
OnChange will come in light when user copy paste the text in values.

solution is that only trim the extra value.


  function CheckMaxLenghtOfMessage(sender, maxlimit) {
     
if (sender.value.length >= maxlimit) {

sender.value = sender.value.substring(0, maxlimit);

}
   }
     <asp:TextBox ID="test" runat="server" onkeyup =" return CheckMaxLenghtOfMessage(this,10)" onchange =" return CheckMaxLenghtOfMessage(this,160)" TextMode="MultiLine"></asp:TextBox>

Thanks
Mahesh k Sharma

Wednesday, March 20, 2013

JavaScriptSerializer DateTime Problem

  JavaScriptSerializer  class Deserialize Method does not return same date time wich is is not returning the value which is Serializing by Serialize Method define in JavaScriptSerializer. At least for me it is bug. :) System.Web.Script.Serialization.



public class SimpleClassWithJavaScriptSerializerError {


private DateTime m_Date;


public DateTime Date {


get { return m_Date; }


set { m_Date = value; }//Code which is giving error.

}

}


public class SimpleClassFixForAjax {


private DateTime m_Date;


public DateTime Date {


get { return m_Date; }


set { m_Date = DateTime.SpecifyKind(value, DateTimeKind.Utc); }//This way can fix the problem

}

}


protected void Page_Load(object sender, EventArgs e) {

System.Web.Script.Serialization.
JavaScriptSerializer javaScriptSerializer = new System.Web.Script.Serialization.JavaScriptSerializer();


DateTime dt = DateTime.Now;


SimpleClassWithJavaScriptSerializerError simpleClassWithJavaScriptSerializerError = new SimpleClassWithJavaScriptSerializerError();

simpleClassWithJavaScriptSerializerError.Date = dt;

Response.Write(

"<br/><br/>This is error from JavaScriptSerializer becase deserilaization is not returning which is value which is serialized.");

Response.Write(
string.Format("<br/> Before serialization: {0}", simpleClassWithJavaScriptSerializerError.Date));


string jsonStr = javaScriptSerializer.Serialize(simpleClassWithJavaScriptSerializerError);


SimpleClassWithJavaScriptSerializerError newInstance = javaScriptSerializer.Deserialize<SimpleClassWithJavaScriptSerializerError>(jsonStr);

Response.Write(
string.Format("<br/> After serialization: {0}",newInstance.Date));


//How to fix

Response.Write(
"<br/>------------------------------------------------------------------------------------------------");

 


SimpleClassFixForAjax simpleClassFixForAjax = new SimpleClassFixForAjax();

simpleClassFixForAjax.Date = dt;

Response.Write(

string.Format("<br/> Before serialization: {0}", simpleClassFixForAjax.Date));

jsonStr = javaScriptSerializer.Serialize(simpleClassFixForAjax);


SimpleClassFixForAjax simpleClassFixForAjaxDes = javaScriptSerializer.Deserialize<SimpleClassFixForAjax>(jsonStr);

Response.Write(
string.Format("<br/> After serialization: {0}", simpleClassFixForAjaxDes.Date));


DateTime d = DateTime.SpecifyKind(new DateTime(), DateTimeKind.Utc);

}

Friday, March 15, 2013

Keyup and Keydown on the LI

When you press arrow key up or down your LI should be heighlighed according to movements of arrow key.
<style type="text/css">/*Add this in master page*/

.selectedMahesh

{

background: #ccc;

}

</style>

<script type="text/javascript">

 

function Test(e) {

var $listItems = $('[id=maheshUL] li');/*Use to get UL selector should be accroding to DOM*/



var key = e.keyCode,

$selected = $listItems.filter('.selectedMahesh'),

$current;

if (key != 40 && key != 38) return;

$listItems.removeClass('selectedMahesh');

if (key == 40) // Down key

{

if (!$selected.length || $selected.is(':last-child')) {

$current = $listItems.eq(0);

}

else {

$current = $selected.next();

}

}

else if (key == 38) // Up key

{

if (!$selected.length || $selected.is(':first-child')) {

$current = $listItems.last();

}

else {

$current = $selected.prev();

}

}

$current.addClass(
'selectedMahesh');/*Add the class which is used to heighligh the element*/

}

</script>

<input id="input_city" type="text" onkeydown="Test(event);" />

<div >

<ul id='maheshUL'>

<li >New York</li>

<li >London</li>

<li >Paris</li>

<li >Sydney</li>

</ul>

</div>