Maximum length of each column

declare @sql varchar(8000), @table sysname
select @table='table_name', @sql=''
select
        @sql=@sql+'select '''+@table+''' as table_name,'''+column_name+''' as column_name,
                                max(len('+column_name+')) as column_length from '+@table+' union all '
from
       information_schema.columns where table_name=''+@table+''
set @sql=left(@sql,len(@sql)-9)
exec(@sql)

Search Procedure name in all databases

1. Use Undocumented procedure

EXEC sp_msforeachdb

'if exists(select ''?'' from ?.sys.objects where name=''your_procedure_name'')

select ''?'' from ?.sys.objects where name=''your_procedure_name'''

2. Use Dynamic SQL

declare @sql varchar(max)

set @sql= ''

select

@sql=@sql+' select '''+name+''' as database_name from '+name+'.sys.objects where name =''procedure_name'' union all' from sys.databases

select @sql=substring(@sql,1,len(@sql)-9)

exec(@sql)

Backup All databases

Create procedure Backup_all_databases

as

Declare @sql varchar(8000)

set @sql=''

select @sql=@sql+

'Backup database '+name+' to disk=''E:\backup\'+name+'_'+convert(varchar(8),getdate(),112)+'.bak'''

from master..sysdatabases

where

name not in ('master','tempdb','model','msdb','pubs','Northwind')

--print @sql

EXEC(@sql)

EXEC Backup_all_databases

Vertical to Horizontal

Have this in Table:
A 2ZS
A RRT
A ABC
B 22R
B 123
C WWW

Need this in query result:
A 2ZS, RRT, ABC
B 22R, 123
C WWW


Create table testtable2
(
ID varchar(25) not null,
name varchar(25) null
)

insert testtable2 values ('A','2ZS')
insert testtable2 values ('A','RRT')
insert testtable2 values ('A','ABC')
insert testtable2 values ('B','22R')
insert testtable2 values ('B','123')
insert testtable2 values ('C','WWW')


select * from testtable2
SELECT t.id,LEFT(nl.namelist,LEN(nl.namelist)-1)  AS name
FROM (SELECT DISTINCT id FROM testtable2) t
CROSS APPLY (SELECT name + ',' AS [text()]
             FROM testtable2
             WHERE id=t.id
             FOR XML PATH(''))nl(namelist)

SQL convert vertical to horizontal

IF OBJECT_ID('tempdb..#Parts', 'U') IS NOT NULL DROP TABLE #Parts

CREATE TABLE #Parts

(

ID int,

ID2 int,

PartNo varchar(10)

)

SET NOCOUNT ON

INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 23921, 'DENT')

INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 26145, 'PLGD')

INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 26145, 'PRRP')

INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 26145, 'SIST')

INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'PRMD')

INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'abc')

INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'def')

INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'ghi')

INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'jkl')

SET NOCOUNT OFF

SELECT * FROM #Parts

SELECT ID, ID2,

Max((Case When RowNumber = 1 Then PartNo Else '' End)) AS Part1,

Max((Case When RowNumber = 2 Then PartNo Else '' End)) AS Part2,

Max((Case When RowNumber = 3 Then PartNo Else '' End)) AS Part3,

Max((Case When RowNumber = 4 Then PartNo Else '' End)) AS Part4

FROM

(

SELECT ID, ID2, PartNo,

ROW_NUMBER() OVER(PARTITION BY ID, ID2 ORDER BY PartNo ASC) AS RowNumber

FROM #Parts

) AS tbl

GROUP BY ID, ID2

Vertical data into Horizontal table (PIVOT)

Create Table tbl_SubCode
(
SubjectCode INT NOT NULL,
Subject Varchar(50) NULL
)

INSERT INTO tbl_SubCode Values (1,'English')
INSERT INTO tbl_SubCode Values (3,'Maths')
INSERT INTO tbl_SubCode Values (4,'Science')
GO
Create Table tbl_MarkDetails
(
AddNo varchar(25) NOT NULL,
SubjectCode INT NULL,
Marks Varchar(50) NULL
)

INSERT INTO tbl_MarkDetails Values ('12340','1','52')
INSERT INTO tbl_MarkDetails Values ('12340','3','100')
INSERT INTO tbl_MarkDetails Values ('12341','1','90')
INSERT INTO tbl_MarkDetails Values ('12341','3','99')



Select * From tbl_SubCode
Select * From tbl_MarkDetails


Select AddNo,[English],[Maths],[Science]
From
(Select A.AddNo,B.Subject,A.Marks
From tbl_MarkDetails AS A, tbl_SubCode AS B
Where A.SubjectCode=B.SubjectCode)PS
PIVOT
(
Max(Marks)
FOR Subject IN
([English],[Maths],[Science])
)AS PT