如何在SQL Server中动态选择表和列？

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.

我试图在SQL Server中创建一个SQL代码,动态选择特定数据库中的所有表,然后为每个表中的每个列,计算缺失值和非空值的数量。我也希望将此结果插入到另一个表中。

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.

我有一个teradata代码相同,我试图转换为SQL Server代码。但是我无法正确地获得动态分配和插入部分。

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!

在这方面的任何帮助都会很棒!谢谢!

2 个解决方案

#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:

不确定是否需要UNION或JOIN,但在任何一种情况下,如果使用多数据库,则可以在另一个数据库中使用由三部分组成的名称:

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

希望有所帮助

#2

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]

#1