CODEWITHSUNDEEP
sqlsql-server
Marco Rosas
Marco Rosas

Reputation: 516

How to select from a list of columns? SQL Server

I have a list of columns attained through a SQL query.

SELECT t.name AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY table_name

Now for each column, I want to query some information about the column and put them together in a table.

Here is the query I would run for a single column, for example.

Select Substring(col1, 1, 1) letter, Count(Substring(col1, 1, 1)) cnt
From tbl
Group By Substring(col1, 1, 1)

What this subquery does is that it attains the count of the first character of records in a column. So for example, there may be 6 records in col1 that start with the letter 'a', 15 records in col1 that start with the letter 'b', etc.

How do I combine these two queries together into one table so I get something like...

Table_Name, Column_Name, letter, cnt

    tbl, col1, a, 6

    tbl, col1, b, 15

    tbl, col2, a, 5

    ...

Upvotes: 3

Views: 3562

Answers (2)

Sahi
Sahi

Reputation: 1484

is this useful?

DECLARE @query VARCHAR(Max)

SELECT @query=COALESCE(@query + ' union all ', '') + CAST('SELECT ''' +TABLE_SCHEMA+''' As [Schema],'''+TABLE_NAME+''' As TableName,'''+COLUMN_NAME+''' As ColumnName, Substring(['+COLUMN_NAME+'],1,1) letter,COUNT(Substring(['+COLUMN_NAME+'],1,1)) cnt
FROM '+TABLE_SCHEMA+'.'+TABLE_NAME+
' Group by Substring(['+COLUMN_NAME+'],1,1)' AS VARCHAR(MAX))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN('varchar','char','nvarchar') 

--SELECT @query
Exec(@query)

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want this :

SELECT t.name AS table_name,
       c.name AS column_name, t1.letter, count(*) as cnt
FROM sys.tables AS t INNER JOIN 
     sys.columns c 
     ON t.OBJECT_ID = c.OBJECT_ID 
CROSS APPLY ( VALUES (Substring(c.name, 1, 1)) ) t1(letter)
GROUP BY t.name, c.name, t1.letter;

Upvotes: 1

Related Questions