Help with a Cursor to Return all attributes in a database

Transact-SQL
1

Hello Forum,
Please forgive me but I am very new to the concept of cursors. I'm trying to create a cursor using Information_schema tables. It should display each distinct field name in the database I'm working in AND the number of times that each field name appears. I'm also trying to order the results in descending row count. This is the code I have so far. I'm receiving multiple error messages and I still can't figure out how to show the results in descending row count. Thank you in advance for your help. -- RM

DECLARE TSQL2012_Cursor CURSOR

FOR
SELECT DISTINCT , COUNT() FROM Information_schema

OPEN TSQL2012_Cursor

            FETCH NEXT FROM TSQL2012_Cursor



            WHILE @@FETCH_STATUS

= 0

                            FETCH NEXT FROM TSQL2012_Cursor

CLOSE TSQL2012_Cursor

DEALLOCATE TSQL2012_Cursor

2

INFORMATION_SCHEMA is a schema, so you cannot query against it. You would query against objects in the schema - for example, INFORMATION_SCHEMA.Tables.

However, without trying to sound pedagogic, you should not be using cursors, and for the most part, avoid INFORMATION_SCHEMA. Instead use the views in sys schema. To find all the columns and table names, you can use a query like shown below:

SELECT
	t.NAME AS TableName,
	c.NAME AS columnName
FROM
	sys.columns c
	INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE
	t.type = 'U'

You can aggregate that anyway you wish to find the names of repeated column names etc.

3

I understand that this is not the optimal way to do to this by using a cursor. I'm just trying to practice with cursors. Can it be done? Thanks.

4

Once when I was at a job interview, one of the questions the guy who was going to be the team lead asked me was if I knew how to use cursors. I said something like, "Yes, I know about cursors, and I have experimented with them, but I have not really used them for anything substantial". Then, I added that "And, I would like to avoid using them if at all possible".

The guy said "That is the best answer you could have given".

So assuming you are doing this for that "know about and experimented with" part, something like the following is what you would do. Like I said, I am no expert on cursors, so if this is wrong, I stand corrected

DECLARE TSQL2012_Cursor CURSOR

FOR
SELECT COLUMN_NAME, COUNT(*) FROM Information_schema.COLUMNS GROUP BY COLUMN_NAME;

OPEN TSQL2012_Cursor
DECLARE @name VARCHAR(256), @N int;
FETCH NEXT FROM TSQL2012_Cursor INTO @name , @n 
WHILE @@FETCH_STATUS = 0
BEGIN 
	SELECT @name, @n;
	FETCH NEXT FROM TSQL2012_Cursor
END
                            
CLOSE TSQL2012_Cursor

DEALLOCATE TSQL2012_Cursor