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

Comparing data type of sql server and ms access in c#

		Jet Engine (Access)	Sql-Server	C#
		Text	char, nchar, varchar, nvarchar	string
		Memo	text, ntext, the above with len>255	string
		Byte	tinyint	byte
		Integer	smallint	short
		Long Integer	integer (int)	int
		Single	real	float
		Double	float	double
		Replication ID	float	Guid
		Decimal	decimal	decimal
		Date/Time	smalldatetime, datetime, datetime2	DateTime
		Binary     (8 bytes)	timestamp, rowversion (Since V2008)	byte[]    (8 bytes)
		Currency	smallmoney, money	decimal
		AutoNumber	int + identity property	int
		Yes/No	bit	bool
		OLE Object	image	byte[]
		Hyperlink	<no equivalent>	string
		<no equivalent>	binary, varbinary	byte[]

Delete All File in Folder from "*.bak" Format File

Method 1:

DECLARE @DateString CHAR(8)

SET @DateString = '04/10/2014'

EXECUTE master.dbo.xp_delete_file 0,  N'E:\10-04-2014\',N'bak',@DateString, 1

 
Method 2:


EXEC sp_configure 'show advanced options', 1
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

EXECUTE master.dbo.xp_cmdshell  'del E:\10-04-2014\Test_db.bak'

Create Folder

EXEC master.dbo.xp_create_subdir '<path>'

Ex: EXEC master.dbo.xp_create_subdir 'E:\10-04-2014\'

How to list files inside a folder with SQL Server

EXEC xp_dirtree '<path>', 10, 1

EX: EXEC xp_dirtree 'E:\10-04-2014\', 10, 1

Get All Sup dir in folder

EXEC master.sys.xp_subdirs 'e:\\'

EXEC master.sys.xp_dirtree 'E:\',1,1;

Current database schema, table name, Column name, data type, max length

Current database schema, table name, Column name, data type, max length, precision, scale, is_nullable, is_ansi_padded

SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema], 
        T.[name] AS [table_name], AC.[name] AS [column_name], 
        TY.[name] AS system_data_type, AC.[max_length],
        AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded]
FROM sys.[tables] AS T 
  INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
 INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id] 
WHERE T.[is_ms_shipped] = 0
ORDER BY T.[name], AC.[column_id]

sql reset id column

  How To: Reset Identity column in SQL Server

Deleted all the existing records

DBCC CHECKIDENT ('TableName', reseed, 0)

Find stored procedures in a database by name, with the number of lines of code for each one.

select t.sp_name, sum(t.lines_of_code) - 1 as lines_ofcode, t.type_desc
from
(
    select o.name as sp_name,
    (len(c.text) - len(replace(c.text, char(10), ''))) as lines_of_code,
    case when o.xtype = 'P' then 'Stored Procedure'
    when o.xtype in ('FN', 'IF', 'TF') then 'Function'
    end as type_desc
    from sysobjects o
    inner join syscomments c
    on c.id = o.id
    where o.xtype in ('P', 'FN', 'IF', 'TF')
    and o.category = 0
    and o.name not in ('fn_diagramobjects', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition', 'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams', 'sysdiagrams')
) t
group by t.sp_name, t.type_desc
order by 1


Ex:

----------------------------------------------------------------------
 sp_name     lines_ofcode     type_desc            
----------------------------------------------------------------------
 sp_Abc               7            Stored Procedure 

 sp_Ddf              14           Stored Procedure  

 sp_Ghi              157         Stored Procedure