Home > Query, SQL > BISQL # 48 : How to Generate SELECT script for all tables in a Database

BISQL # 48 : How to Generate SELECT script for all tables in a Database

 

Hi Friends,

Can you just guess what query we fired @ very first time when we go to SQL Server Management Studio !!

Off course its nothing but Select * from <Some Table> !!

Today I just come across very nice article to get rid of this too when we have lots of table and if you want to always query lots of table with Select * from … and again Select * from … Smile

So what I am going to do for this ??

At the end of this post i.e Third Step I have given direct solution but most important how I have reach till that script ..

We will use following database as shown for this demonstration

image

Step 1 : Get all information about tables in database

This script will Give output as all table info of given DB

 SELECT * FROM sys.tables

Show all details :

image

Step 2 :Get only names of tables in entire database

This script is as simple as above just have to select name form above query

 SELECT NAME FROM sys.tables

This will give output to 29 tables for this DB

image

Step 3 : Now use the following tricky script to simply genrate select * for All table for given db

 SELECT 'SELECT * FROM' +' '+'['+NAME +']'+' '+'GO' FROM sys.tables

Output is as simple as follows

image

Now it just matter of copy paste the things from SSMS output to SSMS query plan

image

I know this diagrams can are quite large but I don’t want to left a single doubt in my readers understanding.

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 49 : SQL Server Denali Feature # 1–FileTables #1 – Introduction,Theory

· BISQL # 50 : SQL Server Denali Feature # 1- FileTables # 2 – Demo , Scripting

· BISQL # 51 : SQL Server Denali Feature # 2 – Sequence number

· BISQL # 51 : SQL Server Denali Feature # 2 – Sequence number –All Explanation !!

· BISQL # 52 : How To Check Database log file Details and Who Access last

 

Hope you will like this post on generating Select script for all tables in Database.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog : Link Resource Website

              For More information related to BI World visit my Mentalist Blog

                          Link Resource Blog >> Daily Interesting links

                          SQL Server Mentalist >> SQL Learning Blog

                          Business Intelligence Mentalist >> BI World

                                      Connect With me on

            | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                   Copyright © 2011 – 2012 Vishal Pawar

Advertisements
Categories: Query, SQL
  1. December 3, 2011 at 1:20 am

    First class writing!!!

    • December 5, 2011 at 8:10 pm

      Hey thnx for u r comment !!!
      visitors comment are jwell for Me !!!

  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: