Get RowCount all tables of a Database in SqlServer

SELECT [TableName] = O.name, [RowCount] =MAX(I.rows)FROM sysobjects O, sysindexes I
WHERE O.xtype ='U'AND I.id =OBJECT_ID(O.name)
GROUP BY O.name ORDER BY [RowCount] DESC

Get current date n time in sqlserver

select current_timestamp
go
select {fn now()}
go
select getdate()
go
select convert(varchar,getdate(),101)*
go
sqlserver2005 -- select convert(varchar(8),getdate(),108), convert(varchar(8),getdate(),101) 
sqlserver2008 -- select convert(time,getdate()), convert(date,getdate(),101)

 *Each style will give the output of the date in a different format.
The default style it uses is 100.
The style values can be ranging between 100-114, 120, 121, 126, 127, 130 and 131 or 0 to 8, 10, 11, 12 and 14 in this case century part will not returned.

change the database name in sqlserver using query

Supported in SQL Server 2000 and 2005
exec sp_renamedb 'databasename' , 'newdatabasename'

Supported in SQL Server 2005 and later version
ALTER DATABASE 'databasename' MODIFY NAME = 'newdatabasename'

select DB_NAME()
select name from sys.databases

sql syntaxes

Select Statement
SELECT "column_name" FROM "table_name"

Distinct
SELECT DISTINCT "column_name" FROM "table_name"

Where
SELECT "column_name" FROM "table_name" WHERE "condition"

And/Or
SELECT "column_name" FROM "table_name" WHERE "simple condition" {[AND|OR] "simple condition"}+

In
SELECT "column_name" FROM "table_name" WHERE "column_name" IN ('value1', 'value2', ...)

Between
SELECT "column_name" FROM "table_name" WHERE "column_name" BETWEEN 'value1' AND 'value2'

Like
SELECT "column_name" FROM "table_name" WHERE "column_name" LIKE {PATTERN}

Order By
SELECT "column_name" FROM "table_name" [WHERE "condition"] ORDER BY "column_name" [ASC,DESC]

Count
SELECT COUNT("column_name") FROM "table_name"

Group By
SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1"

Having
SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1" HAVING (arithematic function condition)

Create Table Statement
CREATE TABLE "table_name" ("column 1" "data_type_column_1", "column 2" "data_type_for_column_2",.... )

Drop Table Statement
DROP TABLE "table_name"

Truncate Table Statement
TRUNCATE TABLE "table_name"

Insert Into Statement
INSERT INTO "table_name" ("column1", "column2", ...) VALUES ("value1", "value2", ...)

Update Statement
UPDATE "table_name" SET "column_1" = [new value] WHERE {condition}

Delete From Statement
DELETE FROM "table_name" WHERE {condition}

select--scalar--string, update,insert,delete--nonquery--int

Ref: 

http://www.1keydata.com/sql/sql-syntax.html

http://www.sqlcommands.net/

joins example

table1                                  table2

col1 col2                         col3   col4

 1      a                              1       a

 2      b                              2       b

null    c                              3       c

 4     null                           null     d

inner join result:

col1 col2 col3 col4

 1      a     1      a

 2      b     2      b

left outer join result:

col1 col2 col3 col4

  1      a     1     a

  2      b      2    b

 null    c    null  null

  4     null  null  null

full outer join result: 

col1 col2 col3 col4

  1      a     1     a

  2      b      2    b

  null    c    null  null

   4     null  null  null

 null    null    3    c

 null    null   null  d

how to find server/system name from sqlserver name using query

select host_name()

filter data of dataset / datatable

ds.Tables[0].Select("Name LIKE %" + "abc%" );
------------------------------
DataView dv = ds.Tables[0].DefaultView;
dv.RowFilter = "lastname like '" + txtName.Text + "%'";
DataTable dt = dv.ToTable();
------------------------------
if (((DataRow[])dt.Select("dname="+newdnam)).Length > 0)
---------------------------------------------------------------
DataTable dt = (DataTable)Session["data"];
DataRow[] drow = dt.Select("id="+id+"");
Label1.Text = drow[0].ItemArray[1].ToString();
---------------------------------------------------------------
dt.Constraints.Clear();
dt.PrimaryKey = newDataColumn[] { dt.Columns["id"] };DataRow[] dr = dt.Rows.Find(val.ToString());

get datetime in user friendly (datetime convertion) in sqlserver

select convert (varchar(10), getdate(),101)
------------------------------------------
ddat = new Date();
ddat = new Date(ddat.setDate(new Date(stdate.value).getDate()+parseInt(days)));
var d=newdate.getDate();
var m=(newdate.getMonth()+1);
var y=newdate.getFullYear();
-----------------------------------------
day = new Date()
day = new Date("August15, 2006 08:25:00")
day = new Date(06,8,15)
day = new Date(06,8,15,8,25,0)

Ref: http://msdn.microsoft.com/en-us/library/ms187928.aspx with convertion chart

 

select convert(varchar, getdate(), 100) convertResult,100 style union
select convert(varchar, getdate(), 101),101 union
select convert(varchar, getdate(), 102),102 union
select convert(varchar, getdate(), 103),103 union
select convert(varchar, getdate(), 104),104 union
select convert(varchar, getdate(), 105),105 union
select convert(varchar, getdate(), 106),106 union
select convert(varchar, getdate(), 107),107 union
select convert(varchar, getdate(), 108),108 union
select convert(varchar, getdate(), 109),109 union
select convert(varchar, getdate(), 110),110 union
select convert(varchar, getdate(), 111),111 union
select convert(varchar, getdate(), 112),112 union
select convert(varchar, getdate(), 113),113 union
select convert(varchar, getdate(), 114),114  union
select convert(varchar, getdate(), 120),120  union
select convert(varchar, getdate(), 121),121  union
select convert(varchar, getdate(), 126),126  union
select convert(varchar, getdate(), 127),127  union
select convert(varchar, getdate(), 130),130  union
select convert(varchar, getdate(), 131),131
order by 2

 

convertResult	style
Mar 18 2016  5:27AM	100
3/18/2016	101
2016.03.18	102
18/03/2016	103
18.03.2016	104
18-03-2016	105
18-Mar-16	106
18-Mar-16	107
5:27:19	108
Mar 18 2016  5:27:19:257AM	109
3/18/2016	110
3/18/2016	111
20160318	112
18 Mar 2016 05:27:19:257	113
05:27:19:257	114
3/18/2016 5:27	120
27:19.3	121
2016-03-18T05:27:19.257	126
2016-03-18T05:27:19.257	127
9 ????? ??????? 1437  5:27:19	130
9/06/1437  5:27:19:257AM	131

 

split last string in sqlserver

username

emp/satya
md/sai
emp/develop/suresh
clerk/surya
comp/sam


select reverse(substring(reverse(username),0,charindex('/',reverse(username)))) from tblemp

how to find out rank of employees based on their salaries

select empname,dense_rank()over(order by salary)as rank from tblEmp

get the serial no. for records of table in sqlserver

select empname, row_number() over(order by empid)as 's.no' from tblEmp

Get the list of columns in a table in sqlserver

select column_name from information_schema.columns where table_name='tblEmp'
or
sp_columns 'tblName'
or
select name from sys.columns where object_id=object_id('tblEmp')
or
select rows from sysindexes where id=object_id('tblEemp') and indid in (0,1)

get the list of tables that r available in a particular database in sqlserver

select * from information_schema.tables
or
select * from sys.tables
or
sp_tables

copy one column data into other column of a same table using query in sqlserver

update tblEmp set address1=address2

how to find out second highest salaried employee details of a table in sqlserver

with s1(id,names,sal) as (select 1 ,'a' ,1.00 union all select 2,'b',2.00 union all select 3,'c',3.00 union all select 4,'d',4.00 union all select 5,'f',5.00)

select * into sam from s1

select * from sam

--get 2nd highest sal.emp

• select top 1 * from sam where sal in (select top 2 sal from sam order by sal desc) order by sal


• select * from sam tbl1 where 1=(select count(distinct sal) from sam tbl2 where tbl1.sal<tbl2.sal)


• select top 1 * from ( select top 2 * from sam order by sal desc)s order by sal


• select * from (select *, rn=row_number() over(order by sal desc) from sam) s where rn=2


• select * from (select *, rn = dense_rank() over(order by sal desc) from sam) s where rn=2

--get 2nd lowest sal.emp

• select top 1 * from sam where sal in (select top 2 sal from sam order by sal) order by sal desc

 

Max possible columns per a table in sqlserver is 1024 but in oracle 1000
Max possible databases : 32,767

sp for search

create proc sp_search(@p varchar(20))
as
begin
select lastname from tblemployee where lastname is not null and lastname like @p+'%'
end
--exec sa_tstname c

GridView data filter with StoredProcedure

SqlCommand cmd=newSqlCommand();
SqlConnection con = new SqlConnection("..your connection string....");
//Data Source=satya;Initial Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=sa123
SqlDataAdapter da=newSqlDataAdapter();
DataSet ds=newDataSet();
cmd.Parameters.Clear();

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_getall";
cmd.Connection = con;
cmd.Parameters.Add("@p", SqlDbType.VarChar, 20).Value = txtSearch.Text;

da = newSqlDataAdapter(cmd);
da.Fill(ds);
DataView dv = ds.Tables[0].DefaultView;

dv.RowFilter = "eName like '" + mr+"'";
gvsearch.DataSource = dv;
gvsearch.DataBind();

create proc sp_getall
as
begin
select*from tblemp
end

------------------------------------

cmd.CommandText = "select * from user_master";
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
dr = cmd.ExecuteReader();
//table, ExecuteScalar - single value object type, ExecuteNonQuery - Count
gv1.DataSource = dr;
gv1.DataBind();
con.Close();