Clear cache before generating the execution plan in SQL Server

Hi,

Are you doing SQL query tuning ???, so you must be generating execution plan...... Ok ..so before generating execution plan of your SQL query you shoud flush the proc out of your database and clear cache/buffers...below are the script for that.

1. Command to flush the proc out of database
DBCC FLUSHPROCINDB (db_id)

here db_id you can find by using following query in you database
Select db_id() -- this will return int number that number you can pass as db_id

2. Clear Cache

DBCC FREEPROCCACHE

2. Clear Buffers

DBCC DROPCLEANBUFFERS

After running above 3 commands in your database, you can generate execution plan, 

now you will  get exact execution plan.

How to generate data script for a table in SQL Server

Hi,

In your SQL Server database you might be having some master tables which contains master data entries of your application,

schema of these tables you can easily generate from SQL Server table properties and generate schema options, now if you want to generate data script of all the existing data in the table then you can use below SQL script to do that work

Say AppRoles table contains following data

AppRoleId	RoleDescription	Status
1	Admin	1
2	User	1
3	Guest	1
4	Demo User	1

select 'insert into tAppRoles (AppRoleID, RoleDescription, Status) values (''' + AppROleId + ''',''' + RoleDescription + ''',' + convert(varchar(1),status) + ')' from tAppRoles


Now when you will run above script in your database it will give you following result set

insert into tAppRoles (AppROleId, RoleDescription, Status) values (1, 'Admin', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (2, 'User', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (3, 'Guest', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (4, 'DemoUser', 1)

So this way you can get insert script of all the records, now just save it ....you are done !!!