Tuesday, July 10, 2007

Columns List of Views

One of myclient asked me nice question about getting columns list for views. Well, please take a look at following solution I came up with.

The first view just holds the data about view and their columns and as you can see it is not easy to read it at all.

CREATE VIEW v_getcolumns
AS
SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME ,COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.COLUMNS
ON INFORMATION_SCHEMA.TABLES.TABLE_NAME=INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA) + '.' +
QUOTENAME(INFORMATION_SCHEMA.TABLES.TABLE_NAME)
), 'IsMSShipped') = 0


The final step I build a crosstab to contacanate the names of the columns per view based on count.I ened upi with 10 columns per view as maximum number, you can modify it for your needs.The cool thing is if you specify '*' in SELECT statement within a view and running the following query you get all columns of the table.

SELECT TABLE_NAME ,MAX(CASE WHEN rnk = 1 then COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 2 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 3 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 4 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 5 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 6 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 7 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 8 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 9 THEN ', ' + COLUMN_NAME ELSE '' end)
+MAX(CASE WHEN rnk = 10 THEN ', ' + COLUMN_NAME ELSE '' end) AS columns
FROM
(
SELECT A.TABLE_NAME ,A.COLUMN_NAME,COUNT(*)rnk
FROM v_getcolumns A, v_getcolumns B
WHERE A.TABLE_NAME=B.TABLE_NAME AND
A.COLUMN_NAME>=B.COLUMN_NAME
GROUP BY a.TABLE_NAME ,a.COLUMN_NAME
) AS der
GROUP BY TABLE_NAME

4 comments:

haiyan said...

When I logged into the hotel, I was surprised that I had 1000 habbo credits which is unbelievable. I logged back in 5 minutes later to discover that all my habbo gold has been stolen. It gives you a virtual environment where you can use your imagination to create your own small cartoony world with your own habbo coins and interact with different people. For decorations you actually pay real monry to buy habbo gold in the range of $0.20, it may sound ridiculous. To use Hotel navigator you need to install cheap habbo credits of macromedia hockave player. There are two types of rooms, public and guest room.

products said...

China Wholesale has been described as the world’s factory. This phenomenom is typified by the rise ofbusiness. Incredible range of products available with China Wholesalers “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and buy products wholesaleChina Wholesale will skyroket your profits.wedding dressescheap naruto cosplayanime cosplay

products said...

Women’s nike tn Shox Rivalry est le modèle féminin le plus tendance de baskets pour le sport. tn chaussuresConcernant la semelle :spyder jacketsCheap Brand Jeans Shop - True Religion Jeans cheap nike shox & Puma Shoes Online- tn nike,Diesel Jeans le caoutchouc extérieur, l’EVA intermédiaire Levis Jeanset le textile intérieur s’associent pour attribuer à la.ed hardy shirts pretty fitCharlestoncheap columbia jackets. turned a pair of double plays to do the trick.Lacoste Polo Shirts, puma basket, Burberry Polo Shirts.wholesale Lacoste polo shirts and cheap polo shirtswith great price.Thank you so much!!cheap polo shirts men'ssweate,gillette mach3 razor bladesfor men.As for Cheap Evisu JeansCheap Armani Jeanspolo shirtsPuma shoes

aiya said...
This comment has been removed by a blog administrator.