SQL Queries

    DECLARE  @id int,@name varchar(100)
    DECLARE cursorTest CURSOR 
    FOR 
    SELECT id,names from error 
    OPEN cursorTest    
    FETCH NEXT FROM cursorTest INTO  @id, @name  
    WHILE @@FETCH_STATUS = 0  
      BEGIN  
        update error set names=@name+CAST(1 as varchar(200))
         where id=@id
		FETCH NEXT FROM cursorTest INTO  @id, @name  
     END   
    CLOSE cursorTest   
    DEALLOCATE cursorTest 

     alter table Table_Name  add    Constraint ConstraintName   default (1) for Column_Name
     

 
 select * from (
select ROW_NUMBER() over(partition by accountname order by accountname) row,* from AQuery
) t where row>1
                                                

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'pricebook'
     

backup database  del to disk='e:\de\del.bak'

RESTORE FILELISTONLY  FROM DISK = 'e:\del\del.bak';

RESTORE DATABASE--
RESTORE DATABASE test2
FROM DISK = 'e:\de\test.bak'
WITH REPLACE, RECOVERY;

declare @dbBakFilePath  varchar(300)=N'E:\ADel\testErp\Temp\NewCustomer.bak' ,
		@dbName varchar(300)='de2',@mdfFileNameOverWrite  varchar(300)=null,--,@mdfFileNameOverWrite  varchar(300)='del3',
		@dbFolderPath varchar(300),@mdfFileName varchar(300),@ldfFileName varchar(300),
	   @mdfFilePath varchar(300),@ldfFilePath varchar(300) 
if(@mdfFileNameOverWrite is null)
 set @mdfFileNameOverWrite=@dbName 

CREATE TABLE #restore_filelistonly (
		LogicalName nvarchar(128),PhysicalName nvarchar(260),Type char(1) NULL,FileGroupName nvarchar(128) NULL,Size numeric(20,0),
		MaxSize numeric(20,0),FileID bigint,CreateLSN numeric(25,0),DropLSN numeric(25,0) NULL,UniqueID uniqueidentifier,ReadOnlyLSN numeric(25,0) NULL,
		ReadWriteLSN numeric(25,0) NULL,BackupSizeInBytes bigint,SourceBlockSize int,FileGroupID int,LogGroupGUID uniqueidentifier,
		DifferentialBaseLSN numeric(25,0) NULL,DifferentialBaseGUID uniqueidentifier NULL,IsReadOnly bit NULL,IsPresent bit NULL,
		TDEThumbprint varbinary(32) NULL,SnapshotURL nvarchar(360) NULL    )

select @dbFolderPath=REPLACE(physical_name,'master.mdf','') from sys.master_files where name ='master'

INSERT INTO #restore_filelistonly (	LogicalName, PhysicalName, Type,FileGroupName,Size, MaxSize, FileID,
	CreateLSN, DropLSN, UniqueID,ReadOnlyLSN, ReadWriteLSN,BackupSizeInBytes, SourceBlockSize, FileGroupID, LogGroupGUID,
	DifferentialBaseLSN, DifferentialBaseGUID,IsReadOnly, IsPresent,TDEThumbprint, SnapshotURL    )
exec('RESTORE FILELISTONLY FROM DISK = '''+@dbBakFilePath+'''')

select  @mdfFileName=LogicalName,@mdfFilePath=@dbFolderPath+@dbName+'.mdf' from #restore_filelistonly where type='D'
select  @ldfFileName=LogicalName,@ldfFilePath=@dbFolderPath+@dbName+'_log.ldf' from #restore_filelistonly where type='L'
  
RESTORE DATABASE @dbName
FROM DISK = @dbBakFilePath
WITH
    MOVE @mdfFileName  TO @mdfFilePath,
    MOVE @ldfFileName TO @ldfFilePath,
    REPLACE, RECOVERY;

EXEC sp_cycle_errorlog ; 

declare @qry varchar(500)
	select 
	 @qry='alter database '+db.name+' set recovery Simple ; DBCC shrinkfile('+lf.name+',1) ;alter database '+db.name+' set recovery Full ;' 
	from sys.databases db
	left outer join sys.master_files  lf on lf.database_id=db.database_id and lf.type=1
	where db.name =DB_NAME(db_id())
	exec (@qry)

SELECT STUFF((
        SELECT ',' + name
        FROM MasterOpportunityStatus
        FOR XML PATH('')
    ), 1, 1, '') AS CommaSeparatedValues;
    

SELECT outerTbl.DocumentNumber SoNumber ,
STUFF((
    SELECT distinct ',' + CAST(innerTbl.Tech as varchar(20))
    FROM SOLogs innerTbl
    WHERE innerTbl.DocumentNumber = outerTbl.DocumentNumber and innerTbl.DocType=5
    FOR XML PATH('')), 1, 1, '') as Reps
FROM SOLogs outerTbl where outerTbl.DocType=5
GROUP BY outerTbl.DocumentNumber