Home arrow SQL Server arrow Page 4 - Execute SQL on Multiple Tables/Columns - New Feature in SQL Server 2000
SQL SERVER

Execute SQL on Multiple Tables/Columns - New Feature in SQL Server 2000


In this latest article, Raghavendra introduces us to a feature available to us in SQL Server, allowing us to execute SQL statements on multiple tables and columns.

Author Info:
By: Raghavendra Narayana
Rating: 4 stars4 stars4 stars4 stars4 stars / 53
September 25, 2003
TABLE OF CONTENTS:
  1. · Execute SQL on Multiple Tables/Columns - New Feature in SQL Server 2000
  2. · Script
  3. · Parameters
  4. · Examples
  5. · Expansion
  6. · Conclusion

print this article
SEARCH DEVARTICLES

Execute SQL on Multiple Tables/Columns - New Feature in SQL Server 2000 - Examples
(Page 4 of 6 )

1. This example uses NORTHWIND database.

This example updates data on all tables where the column name contains “Name” in their names.  The data being changed here is “Ltd.” to “LIMITED” by searching all the tables plus only those columns that contains word “NAME” in their names.

2. This example uses NORTHWIND database.

This example checks the existence of “QUICK-stop” in all the tables whose column names contain “NAME”.  This is an example of executing a set of SQL statements.  A temporary table is created before Executing SP_execSQLonDB. For SQL input parameters, a set of SQL statements are supplied, which simply checks row count for the “QUICK-Stop” string.  If the count is more than one, it then inserts the found Table Name, Column Name and Row Count into the temporary table ##TMP1.

3. Example to supply a Stored Procedure as an SQL Statement for SP_execSQLonDB.

This example executes the USP_DeptStats stored procedure on all the tables that start with “Employee” plus columns that start with “DEPT”.  The @TABLENAME, @COLUMNNAME in the SQL parameters correspond to running Table Name and Column Name respectively as per the result set of the Cursor declaration.

4. Example to supply multiple Table and Column names to @TABLENAME, @COLUMNNAME input parameters.

This will execute the “USP_EMPLOYEE_PF” procedure on all the tables whose name start with either “Employee” or “PF”, on all the columns whose names are either “SALARY” or contain the word “AMOUNT”.


blog comments powered by Disqus
SQL SERVER ARTICLES

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables – New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials