QueriesComplex

create table #temp (table_name varchar(200),dataCount int)
create table #tempTable (dataCount int)
truncate table #temp
truncate table #tempTable
DECLARE   @counts int=0,@name varchar(300)
select  @counts =count(*) from  CompanyAccounts
DECLARE cursorTest CURSOR 
FOR 
select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' --and TABLE_SCHEMA ='dbo'
						and TABLE_NAME not like 'Master%' order by TABLE_NAME
OPEN cursorTest    
FETCH NEXT FROM cursorTest INTO    @name  
WHILE @@FETCH_STATUS = 0  
    BEGIN  
	truncate table #tempTable
	exec ('insert into #tempTable select count(*) from '+@name)
    select  @counts=dataCount  from    #tempTable
	if(@counts>0)
	    begin
		insert into #temp (table_name,dataCount) values (@name,@counts)
		end
	FETCH NEXT FROM cursorTest INTO    @name  
    END   
CLOSE cursorTest   
DEALLOCATE cursorTest 

select * from #temp
drop table #temp
drop table #tempTable

     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)