Home > SQL Server > Construct SQL query using catalog views – SQL Server

Construct SQL query using catalog views – SQL Server


I recently worked on a project where there are some SQL statement that I need to write to insert, update and Delete the data in all columns in a set of tables. There may be some efficient way to implement this task. But, I found one simple solution for my scenario.

I have used SQL catalog views to build my SQL Statements. My scenario was to update the columns by replacing a character in it.

Example for building SQL Queries using catalog view

select ‘UPDATE ‘ + OBJECT_NAME(object_id) +’ SET ‘ + name + ‘ = REPLACE(‘ + name +’, ”+”,””)’ from sys.columns
where left(OBJECT_NAME(object_id), 3) = ‘tbl’

This query generates SQL update statements to replace character + to nothing in each column in all the tables starts with ‘tbl’ that I specified in the where clause.

Execution

pic1there could be some very good way to do this. but found it is very quick solution for me. This is not the stopping point we can also use other object catalog views to accomplish our task quickly and simply.

object catalog views for reference

pic2

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: