DROP PROCEDURE statements (all stored procedures delete Query generate)

SELECT 'DROP PROCEDURE [' + SCHEMA_NAME(p.schema_id) + '].[' + p.NAME + ']'
FROM sys.procedures p 

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