How to dynamically select tables and columns in SQL Server?

Question

I am trying to create a SQL code in SQL Server that dynamically selects all the tables in a specific database and then for each column in each table, counts the number of missing values and non-null values. I also want this result inserted into another table.

Is there any way I can do this without manually changing the column names for each:

Table Name - Column selection

I have a teradata code for the same which I tried to convert to SQL Server code. But I am unable to get the dynamic allocation and insertion parts right.

insert into temp
values (select  ''CAMP'',
    rtrim(''' || tablename || '''),
    rtrim(''' || columnname || '''),
    rtrim(''' || columnformat || '''),
    count(1),
    count(rtrim(upper(case when ' || columnname || '='''' then NULL else '|| columnname ||' end))),
    (cast (count(rtrim(upper(case when  ' || columnname || '='''' then NULL else  ' || columnname || ' end))) as float) / (cast (count(1) as float))) * 100,
    count(distinct rtrim(upper(case when ' || columnname || '='''' then NULL else '|| columnname ||' end))),
    min(rtrim(upper(case when ' || columnname || '='''' then NULL else '|| columnname ||' end))),
    max(rtrim(upper(case when ' || columnname || '='''' then NULL else '|| columnname ||' end))),
    min(len(rtrim(upper(case when ' || columnname || '='''' then NULL else '|| columnname ||' end)))),
    max(len(rtrim(upper(case when ' || columnname || '='''' then NULL else '|| columnname ||' end))))
from    ' || tablename ||')

Any help on this front would be great! Thanks!

I edited the question to include what I tried. Any help would be great. — Danica Fernandes
– Danica Fernandes, Commented Jul 16, 2015 at 10:30
Instead of selecting data from different table and inserting them in another table, you can try view in SQL for this. — Sabyasachi Mishra
– Sabyasachi Mishra, Commented Jul 16, 2015 at 10:37
I am trying to use this to create a view of all the existing tables and some basic information about them. This will help me get an understanding of the kind of data I have in all my tables. — Danica Fernandes
– Danica Fernandes, Commented Jul 16, 2015 at 10:39

Sabyasachi Mishra · Accepted Answer · 2015-07-16 11:11:33Z

1

Not sure if you need a UNION or a JOIN, but in either case you can just use a three-part name for the object in the other database if you are using multi-database:

USE database1; // Your database name
GO
CREATE VIEW dbo.MyView
AS
    SELECT columns FROM dbo.Table1
    UNION ALL
    SELECT columns FROM database2.dbo.Table2; //second database
GO

select * from dbo.MyView // Getting all data from view

Hope that helps

edited Jul 16, 2015 at 11:11

answered Jul 16, 2015 at 10:46

Sabyasachi Mishra

1,7513 gold badges32 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom · Accepted Answer · 2015-07-16 10:53:32Z

0

Does something like this help you:

    SELECT  [Table] = t.[name]
        , [Column] = c.[name]
    FROM    sys.tables t
    INNER   JOIN    sys.columns c
        ON  c.[object_id] = t.[object_id]

answered Jul 16, 2015 at 10:53

Tom

7475 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to dynamically select tables and columns in SQL Server?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related