Drop (delete) all tables In Database

Drop (delete) all tables In Database

exec sp_MSforeachtable 'DROP TABLE ?'

sql table backup same db with new name query

SELECT *
INTO <new table name>
FROM <Source table name>;

The transaction log for database '...' is full due to 'LOG_BACKUP'.

USE [master]
GO 
ALTER DATABASE  <db Name> SET RECOVERY full 
GO
ALTER DATABASE <db Name> SET RECOVERY SIMPLE WITH NO_WAIT;
GO

All database backup to directory

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name


-- specify database backup directory
SET @path = 'C:\Backup\' 


-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)


DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases


OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  


WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 


       FETCH NEXT FROM db_cursor INTO @name  
END  


CLOSE db_cursor  
DEALLOCATE db_cursor

Remove All stored procedure (SP) in DB use query

use <db Name>
Go

declare @procName varchar(500)
declare cur cursor

for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
    exec('drop procedure ' + @procName)
    fetch next from cur into @procName
end
close cur
deallocate cur

Get all table structure from database

Select  t.Table_Schema,
        t.Table_Name,
        c.Column_Name,
        IsNull(c.Column_Default, '') as 'Column_Default',
        c.Is_Nullable,
        c.Data_Type,
        IsNull(c.Character_Maximum_Length, IsNull(Numeric_Precision,'') + IsNull(Numeric_Scale, IsNull(DateTime_Precision,''))) as 'Size'

From Information_Schema.Tables t

Join Information_Schema.Columns c on    t.Table_Catalog = c.Table_Catalog
                                And     t.Table_Schema = c.Table_Schema
                                And     t.Table_Name = c.Table_Name

Where t.Table_Type = 'BASE TABLE'

Order by t.Table_Schema, t.Table_Name, c.Ordinal_Position

SQL Server Services Start

1. Windows + R:  mstsc
2. Login server
3. Windows + R: services.msc
4. Select Following Services 

1. Sql Server (MSSQLSERVER)
2. SQL Server Agent (MSSQLSERVER)
3. SQL Server Analysis Services (MSSQLSERVER)
4. SQL Server Browser
5. SQL Server Interation Services 10.0
6. SQl Server Reporting Services (MSSQLSERVER)
7. SQL Server VSS Writer

 All Services one by one Right click  and Select Properties

   5. Select Logon -> Enter  Password  and Confirm Password -> Click OK

6. Right click services --> Start services

7. 5 and 6 step repeat all  (7 ) services