SQL Server - Tips

List All Tables of Database

 

USE [YourDBName]
GO 
SELECT *FROM sys.Tables
GO

Insert structure from one table to another

SELECT *
into new_table FROM origin_table where 1 =2 

How to create a counter

Use the function ROW_NUMBER ( ), where the syntaxis is

ROW_NUMBER ( ) OVER ( FIELD order_by_clause )

Where FIELD  is the name of the column you want to use to start counting. Example:

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row, 
FROM Sales.vSalesPerson

Enumerate rows ... 1.2.3....

ROW_NUMBER() over ( ORDER BY [Job Stage]) as [pyramidPosition]

Reset the enumeration  ..... 1.2.3....1.2.3

 

ROW_NUMBER() over (partition BY idProject ORDER BY [Job Stage]) as [pyramidPosition]

http://sqlfiddle.com/#!6/501c0/2/0

 Update Table from Select

Update projects

set projects.tg4Real = [TG4 Date Real DD/MM/YYYY] ,
 projects.tg5Real = [TG5 Date Real DD/MM/YYYY] 
from
 projects p
inner join
 gtt...[Upload$] u
on p.projectName = [Project Name]
 

Multi user

ALTER DATABASE DATA_BASE SET MULTI_USER; 

Add user to a DB

Can you check it by Going To Security --> Login-->Right and Click Property and then goto user mapping tab. Then select the database

Convert String Dates to Date/Datetime 

From '12/10/25' to date 

CONVERT(DATE,CAST ( RTRIM( LTRIM([CREATION DATE])) as nvarchar), 103) 

From '16/01/2007 3:03:35:270AM' to datetime

From '23/06/2014 09:07:17 a. m.'  to datetime

CONVERT(datetime, ( RIGHT(LEFT([START TIME],5),2) + '/'+LEFT([START TIME],2)+'/'+ RIGHT(LEFT([START TIME],10),4)+  SUBSTRING([START TIME],11, 9 ) + REPLACE( SUBSTRING([START TIME],21, 4 ), '. ', '' ) ) , 120 ) as ODBC_datetime

 Add many columns in one

Function:  STUFF

select distinct t1.userId, t1.userName,
  STUFF((SELECT ', ' + t2.vpn 
         from Request t2
         where t1.userId = t2.userId
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,0,'') VPN,
        t1.managerName, t1.managerUserId, t1.ccName, t1.ccUserId
from Request t1
order by [userId]

Substract Dates

DATEDIFF( minute, [resto_rst_prov_date], crea_date))

Days with decimals

DATEDIFF( minute, [resto_rst_prov_date], crea_date))/(60*24.0)

Days with two decimals

convert(decimal(12,2), (DATEDIFF( minute, [resto_rst_prov_date], crea_date))/(60*24.0))