.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to take the scripts of the changed SPs/Views/Temp tables/Functions automatically?

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :Sql Server


  We have a huge Database with more than 100 tables , more than 150 SPs.We are 10 members and we will be creating/deleting/updating SPs , functions, views, tem tables  everyday. Actually at the end of the day, we have to send the  scripts of latest SPs,functions,views,Temp tables to the server. So far what we are doing is  we manually go to the SQL server Scripts Wizard and selecting one by one all the SPs,functions,TTables and views. Is there any quicker way to take the changed stuffs instead going manually?

View Complete Post

More Related Resource Links

Sql Scripts - Delete all Tables, Procedures, Views and Functions


In a shared environment you typically don't have access to delete your database, and recreate it for fresh installs of your product. 

I managed to find these scripts which should help you clean out your database.

Use at your own risk.


Delete All Tables

--Delete All Keys


The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common ta


Any ideas how I can order inside the subquery? I get the following error. I have tried ORDER BY MAX([Count]) but it doesn't like this much either... Also, I don't want to use TOP in the subquery as I need to use all the records!

			 COUNT(*) AS [Count]
		GROUP BY System_User_ID
		ORDER BY [Count]) l
	INNER JOIN aspnet_Membership m
	ON CAST(m.UserId AS VARCHAR(36)) = l.System_User_ID
Msg 1033, Level 15, State 1, Line 11
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Using SQL Views like Tables from DB

Unfortunatrly I've not been able to google what I'm looking for, so either its not a problem for others or there is to much other "noise" so what 'm looking for doesn't show up.Using .net 4, MVC 3 I can pull data from a table in SQL Server as follows:        public ActionResult IndexTelDataset()        {            // Table: Customer             CustomersTableAdapter ta = new CustomersTableAdapter();            NorthwindDataset.CustomersDataTable Customers = new NorthwindDataset.CustomersDataTable();            ta.Fill(Customers);             // Return view              return View(Customers);        }How would I do the same using a vew called vwCustomerDetails? Also on the same topic whats the best way to do a query such as "Select * from Customers".  Thanks!Quentin

Enterprise t-sql Stored Procs against tables -vs- Views

I work for a very large enterprise and I am wanting to know what is the best way to design t-sql for the stored proces to be used from an application? Option 1 - Be sure to optimize your Database design so your application will only require at most 6 joins and used stored procs for queries using proper indexs to gain the most performance Option 2 - Create Views and use stored procs against the views My questions:  1. What is the perfromance hit in using views.   My thought process is that views should only be used when you know the data you want to return "hence the name 'VIEW', should not be used as a query".   2. Are stored procs against the schema tables the best for performance. 3. What is the best way to tune and ensure your stored proc is optimal with the correct indexes, so you build a lighting fast query? http://www.mmwebs.com

creating temp tables!

hi friends, 1. Is the tempdb in teh SQL server (2005) is a global database that is shared among other user defined databases? 2. Is is possible to create User defined temp databases like tempdb and how? i have create a temp table create table #usr_with_rls (idx int primary key, usr_name nvarchar(50), f_name nvarchar(50), l_name nvarchar(50), desig nvarchar(50)) and it was created in the tempdb database.  but the sp that creates the this table is in another database, therefore 3. How do i create this temp table "urs_with_rls" in my datbase and not in the tempdb? 4. I need to remove this temp datanase "urs_with_rls" from the server, onces teh sp completes it execution, how do i do that? thanks    

Control positions in the Form are changed automatically.

Hi All, I am developing an UI Form which is having many text, label, combo box controls.  As i have many controls to be placed in the screen, so the form is exceeding one page and i need to scroll. Problem i am facing here is, when every i execute the form all the controls are relatively moved down wards by leaving a blank space on top.   Please help me how to come out of this problem, and i am very much new to C# UI development.   Thank you in Advance.

Does a Maintenance Plan "Rebuild Index" task on Tables + Views rebuild the full text indexes ?


As subject really.

I'm talking about a complete rebuild - not an incremental, or a re-organise.

SQL 2005

The full text indexes are only on individual tables, not Views.

I've asked a similar question before, but sufficiently different, and other is marked as Answered so thought I'd open a new Q : http://social.msdn.microsoft.com/Forums/en/sqltools/thread/5f17f4f1-fba9-436f-aedf-cfbfd89c8db2)


use temp tables instead of CTE's


I need to re write this query with CTE's to reflect differences in DNC ($$) between my @mindt and @maxdt

do I need to show a DNC @ MIN Date and DNC @ Max DT so i can calculate the differerence...can you help?

declare @mindt datetime
declare @maxdt datetime

set @maxdt = getdate()
set @mindt = getdate()-14;

With itmlvl as (



select RB.RBT_TYP_ID
	, rb.DSCR, rp.AMT
	, splr.EM_ITEM_NUM

from geprs_dnc..t_rebate rb 

Staging vs Temp tables in SSIS

Hi SSIS Guru,

Currently, we have our own internal app to pull data from the internet nightly to serverA. On serverA, there is a huge stored precedure doing update, insert and delete by joining lots of tables on serverB via synonyms over LINKED SERVER. However, there are only 3-4 tables on serverA used to join tables on serverB.

I am rewritting this process to eliminate linked server and use SSIS instead of stored procedure. What is the best option to bring tables from serverA to serverB? Should I create staging tables on serverB and refresh tables with TRUNCATE or use temp table instead?

Thanks much,
Staging vs Temp tables in SSIS

temp tables!


hi friend,

assume i have created a temp table, #t1 and after completion of query execution in the sp i can delete it from db.

in multi user enviorement, each user who acess that sp creates a temp table, i know teml tables has a structre #_______________<number>  so when each user access this sp, does it create a seperate temp table for each call?



How to combine 3 Fact Tables into 1: UNION in TSQL vs. MDX Scripts


Hi All,

I have 3 Fact Tables I would like to combine them into 1, I am not sure what's the best way to do it.

In Adventure Works, it uses UNION in TSQL to create a sales_summary Fact from Internet Sales and Reseller Sales.

Can I just write a simple Calculated measure?  What's the best way to handle this kind of situation?







[Distribution Partner].[Distribution Partner].[DP]

Better performance when all temp tables are at start of proc - STILL DON'T SEE WHY


I've already posted on this:


and was convinced even more by what was written in:



BUT when I actually tested a simple script with a profiler there was no difference in the number of deferred compiles (which is two) when all tables were at the start and when tables were spread in the procedure (According to the theory the second stored proc below should have produced only one deferred compile). This script completely contradicts the technical page link above. How can it be explained?




Can I have 3 different tables in a report? And, can I pass 3 temp table to SSRS as source for these

I'm using BI 2008 and sql 2005 and 2008.  Is there an example to show me?  Thanks.

Retrieving changed/updated data from all tables in database



I'm looking for some advise on how I could solve the problem I face, I have various instances of sqlexpress 2005 in different remote locations which are not accessible via internet vpn etc as they are all in corporate networks that will not allow access. What I think I need to do is create some sort of process/service that can be installed on the client and run at a set time/date that collects all the data from theses instances and then push the changes to our remote instance of sql server. Can anyone possibly point me in the right direction of various technologies I should be looking into to solve this problem. All clients would be running windows XP. I have been searching for a few day's but I can't find anything to get me going. If this is not the right forum for this type of question can you suggest any?


Thank you.

workflow runs when item changed even though only 'Automatically start ... is changed' is unselecte


We have an SPDesigner workflow on a form library in SP2007 SP2 (& some CU updates).

Its 'Automatically start this workflow whenever an item is changed' is unchecked.  The other 2 options '...new item is created.' and '... manually started...' are checked.

We add an item into the list.  The workflow starts.  We see the 'Approval Workflow Started' in the workflow history.  And approver then approves the item, and in the workflow history, we then see the 'Approval Workflow Started' again.  It's as if the workflow has restarted. 

This errant behavior seems to have started when we added an if condition in second step of our workflow:

If Checked Out to is not empty

 Log xxxyyy to workflow history

 then Wait for Checked Out To to be empty 

Is it a known bug in SP2007?


A little more info on this:

We have a workflow step that :

Store https://url/blah in Variable:Blah URL

  then Check out item in OurFormLibraryName

  then Set Approval Status to Awaiting Approvers Approval

  then Set ... another field value ...

  then Check in item OurFor

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend