Iterate, cursor and Loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Iterate, cursor and Loop

Author

Topic

Tampapat
Starting Member

18 Posts

Posted - 2012-09-05 : 10:13:39

I am trying to get the names and count of all the fields in a DB. I was told the best way is to Iterate,cursor and loop the sys.table and sys.sys fields. Can someone tell me the best way to do this please. I am new at SQL

Patrick Palmer

edit: moved to proper forum, originally in SQL Server 2000

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-05 : 10:26:20

No need for cursors, loops or iteration, just use some dynamic SQL:

DECLARE @sql varchar(8000)
SET @sql=''
SELECT @sql=@sql+replace(',count([?]) [?] ','?',column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='myTable'
SET @sql='SELECT ' + STUFF(@sql,1,1,'') + ' FROM myTable'
EXEC(@sql)

That works in SQL 2000 (since you posted in that forum). There are some improvements for SQL 2005+ but they ultimately generate the same code. You may want to use varchar(max) instead of varchar(8000) if you have a lot of columns.

Tampapat
Starting Member

18 Posts

Posted - 2012-09-05 : 10:55:52

Sorry it is in SQL 2008

quote:
Originally posted by robvolk

No need for cursors, loops or iteration, just use some dynamic SQL:
DECLARE @sql varchar(8000)
SET @sql=''
SELECT @sql=@sql+replace(',count([?]) [?] ','?',column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='myTable'
SET @sql='SELECT ' + STUFF(@sql,1,1,'') + ' FROM myTable'
EXEC(@sql)
That works in SQL 2000 (since you posted in that forum). There are some improvements for SQL 2005+ but they ultimately generate the same code. You may want to use varchar(max) instead of varchar(8000) if you have a lot of columns.

Patrick Palmer

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-05 : 11:33:39

It will still work.

Tampapat
Starting Member

18 Posts

Posted - 2012-09-05 : 11:37:41

Thank you however, I am getting an error taht states, "invlaid column name 'lastname'

USE MDW

DECLARE @sql varchar(max)
SET @sql=''
SELECT @sql=@sql+replace(',count(

)

','?',lastname)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='myTable'
SET @sql='SELECT ' + STUFF(@sql,1,1,'') + ' FROM dbo.dimphysician'
EXEC(@sql)

quote:
Originally posted by robvolk

It will still work.

Patrick Palmer

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-05 : 11:57:56

Try this:

USE MDW

DECLARE @sql varchar(max)
SET @sql=''
SELECT @sql=@sql+replace(',count([?]) [?] ','?',COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='dimphysician'
SET @sql='SELECT ' + STUFF(@sql,1,1,'') + ' FROM dbo.dimphysician'
EXEC(@sql)

Did you only want to count the lastname column? Or all columns in the table?

Tampapat
Starting Member

18 Posts

Posted - 2012-09-05 : 12:05:48

All columns please.

quote:
Originally posted by robvolk

Try this:
USE MDW

DECLARE @sql varchar(max)
SET @sql=''
SELECT @sql=@sql+replace(',count([?]) [?] ','?',COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='dimphysician'
SET @sql='SELECT ' + STUFF(@sql,1,1,'') + ' FROM dbo.dimphysician'
EXEC(@sql)
Did you only want to count the lastname column? Or all columns in the table?

Patrick Palmer

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-05 : 12:20:32

That should do it then.

Tampapat
Starting Member

18 Posts

Posted - 2012-09-05 : 13:07:51

Perfect, now what if I want all tables in the Data Base MDW not just the one table?

quote:
Originally posted by robvolk

That should do it then.

Patrick Palmer

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-05 : 13:43:23

Looks ugly but works:

USE MDW

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+REPLACE(REPLACE('SELECT ''?.!'' tablename, count(*) rows, ' + 
STUFF((SELECT REPLACE(',count([?]) [?] ','?',COLUMN_NAME)
	FROM INFORMATION_SCHEMA.COLUMNS c
	WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
	FOR XML PATH('')),1,1,'') + 
' FROM ?.!;','?',t.TABLE_SCHEMA),'!',t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE='BASE TABLE'

EXEC(@sql)

Tampapat
Starting Member

18 Posts

Posted - 2012-09-05 : 14:21:23

Another error....
Msg 8117, Level 16, State 1, Line 1
Operand data type ntext is invalid for count operator.

quote:

Originally posted by robvolk

Looks ugly but works:

USE MDW

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+REPLACE(REPLACE('SELECT ''?.!'' tablename, count(*) rows, ' + 
STUFF((SELECT REPLACE(',count([?]) [?] ','?',COLUMN_NAME)
	FROM INFORMATION_SCHEMA.COLUMNS c
	WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
	FOR XML PATH('')),1,1,'') + 
' FROM ?.!;','?',t.TABLE_SCHEMA),'!',t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE='BASE TABLE'

EXEC(@sql)

Patrick Palmer

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-05 : 14:35:22

[code]DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+REPLACE(REPLACE('SELECT ''?.!'' tablename, count(*) rows, ' +
STUFF((SELECT REPLACE(',COUNT(' + CASE WHEN c.DATA_TYPE IN('text','ntext','image')
THEN 'SUBSTRING([?],1,1)' ELSE '[?]' END + ') [?] ','?',COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
FOR XML PATH('')),1,1,'') +
' FROM ?.!;','?',t.TABLE_SCHEMA),'!',t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE='BASE TABLE'

EXEC(@sql)[/code]If you can you should change your table structures to use varchar(max) or nvarchar(max) instead of text/ntext.

Tampapat
Starting Member

18 Posts

Posted - 2012-09-05 : 14:39:07

Perfect you are the best and I'll see about the table structure.

quote:

Originally posted by robvolk

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+REPLACE(REPLACE('SELECT ''?.!'' tablename, count(*) rows, ' + 
STUFF((SELECT REPLACE(',COUNT(' + CASE WHEN c.DATA_TYPE IN('text','ntext','image')
	THEN 'SUBSTRING([?],1,1)' ELSE '[?]' END + ') [?] ','?',COLUMN_NAME)
	FROM INFORMATION_SCHEMA.COLUMNS c
	WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
	FOR XML PATH('')),1,1,'') + 
' FROM ?.!;','?',t.TABLE_SCHEMA),'!',t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE='BASE TABLE' 

EXEC(@sql)

If you can you should change your table structures to use varchar(max) or nvarchar(max) instead of text/ntext.

Patrick Palmer

Tampapat
Starting Member

18 Posts

Posted - 2012-09-06 : 13:43:33

Now we are looking for a North South output not East West. How can I change this query to make it Table|Name|Count|?

quote:
Originally posted by Tampapat

Perfect you are the best and I'll see about the table structure.

quote:
Originally posted by robvolk

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+REPLACE(REPLACE('SELECT ''?.!'' tablename, count(*) rows, ' +
STUFF((SELECT REPLACE(',COUNT(' + CASE WHEN c.DATA_TYPE IN('text','ntext','image')
THEN 'SUBSTRING([?],1,1)' ELSE '[?]' END + ') [?] ','?',COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
FOR XML PATH('')),1,1,'') +
' FROM ?.!;','?',t.TABLE_SCHEMA),'!',t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE='BASE TABLE'

EXEC(@sql)
If you can you should change your table structures to use varchar(max) or nvarchar(max) instead of text/ntext.

Patrick Palmer

Patrick Palmer

Tampapat
Starting Member

18 Posts

Posted - 2012-09-06 : 14:35:30

How do I list my SQL output verticaly down for each column

quote:
Originally posted by Tampapat

Now we are looking for a North South output not East West. How can I change this query to make it Table|Name|Count|?

quote:
Originally posted by Tampapat

Perfect you are the best and I'll see about the table structure.

quote:
Originally posted by robvolk

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+REPLACE(REPLACE('SELECT ''?.!'' tablename, count(*) rows, ' +
STUFF((SELECT REPLACE(',COUNT(' + CASE WHEN c.DATA_TYPE IN('text','ntext','image')
THEN 'SUBSTRING([?],1,1)' ELSE '[?]' END + ') [?] ','?',COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
FOR XML PATH('')),1,1,'') +
' FROM ?.!;','?',t.TABLE_SCHEMA),'!',t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE='BASE TABLE'

EXEC(@sql)
If you can you should change your table structures to use varchar(max) or nvarchar(max) instead of text/ntext.

Patrick Palmer

Patrick Palmer

Patrick Palmer

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-07 : 10:15:55

Someone will have to come along and do a dynamic UNPIVOT. I'll try it later today but can't guarantee an answer, I'm traveling this weekend.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-09 : 00:31:52

[code]DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+REPLACE(REPLACE('SELECT ''?.!'' tablename, count(*) rows, ' +
STUFF((SELECT REPLACE(',COUNT(' + CASE WHEN c.DATA_TYPE IN('text','ntext','image')
THEN 'SUBSTRING([?],1,1)' ELSE '[?]' END + ') [?] ','?',COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
FOR XML PATH('')),1,1,'') +
' FROM ?.! UNPIVOT(val FOR Name IN(' +
STUFF((SELECT ','+quotename(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
FOR XML PATH('')),1,1,'') +
')) a;','?',t.TABLE_SCHEMA),'!',t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE='BASE TABLE';

EXEC(@sql);[/code]Warning: not tested

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-10 : 10:52:04

Fixed and tested:

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+REPLACE(REPLACE('WITH cte as (SELECT ''?.!'' tablename, count(*) rows, ' + 
STUFF((SELECT REPLACE(',COUNT(' + CASE WHEN c.DATA_TYPE IN('text','ntext','image')
	THEN 'SUBSTRING([?],1,1)' ELSE '[?]' END + ') [?] ','?',COLUMN_NAME)
	FROM INFORMATION_SCHEMA.COLUMNS c
	WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
	FOR XML PATH('')),1,1,'') + 
' FROM ?.!) SELECT * FROM cte UNPIVOT(val FOR column_name IN([rows],' +
STUFF((SELECT ','+QUOTENAME(COLUMN_NAME)
	FROM INFORMATION_SCHEMA.COLUMNS c
	WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
	FOR XML PATH('')),1,1,'') +
')) a;','?',t.TABLE_SCHEMA),'!',t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE='BASE TABLE';

EXEC(@sql);

Tampapat
Starting Member

18 Posts

Posted - 2012-09-10 : 15:37:42

Msg 8156, Level 16, State 1, Line 1
The column 'TableName' was specified multiple times for 'cte'.

quote:

Originally posted by robvolk

Fixed and tested:

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+REPLACE(REPLACE('WITH cte as (SELECT ''?.!'' tablename, count(*) rows, ' + 
STUFF((SELECT REPLACE(',COUNT(' + CASE WHEN c.DATA_TYPE IN('text','ntext','image')
	THEN 'SUBSTRING([?],1,1)' ELSE '[?]' END + ') [?] ','?',COLUMN_NAME)
	FROM INFORMATION_SCHEMA.COLUMNS c
	WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
	FOR XML PATH('')),1,1,'') + 
' FROM ?.!) SELECT * FROM cte UNPIVOT(val FOR column_name IN([rows],' +
STUFF((SELECT ','+QUOTENAME(COLUMN_NAME)
	FROM INFORMATION_SCHEMA.COLUMNS c
	WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
	FOR XML PATH('')),1,1,'') +
')) a;','?',t.TABLE_SCHEMA),'!',t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE='BASE TABLE';

EXEC(@sql);

Patrick Palmer

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-10 : 16:35:23

Try this version, I fixed a few bugs and renamed some internal columns:

DECLARE @sql NVARCHAR(MAX)=N'';
SELECT @sql=@sql+REPLACE(REPLACE(N'WITH cte as (SELECT ''?.!'' table_name,count(*) [rows],' + 
STUFF((SELECT REPLACE(N',COUNT(' + CASE WHEN c.DATA_TYPE IN('text','ntext','image')
	THEN N'SUBSTRING(?,1,1)' ELSE N'?' END + N') ? ',N'?',QUOTENAME(COLUMN_NAME))
	FROM INFORMATION_SCHEMA.COLUMNS c
	WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
	FOR XML PATH('')),1,1,N'') + 
N' FROM ?.!) SELECT * FROM cte UNPIVOT(row_count FOR column_name IN([rows],' +
STUFF((SELECT ','+QUOTENAME(COLUMN_NAME)
	FROM INFORMATION_SCHEMA.COLUMNS c
	WHERE c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
	FOR XML PATH('')),1,1,N'') +
N')) a;','?',QUOTENAME(t.TABLE_SCHEMA)),N'!',QUOTENAME(t.TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE=N'BASE TABLE';

EXEC(@sql);

I've tested it against a dozen or so databases, if you still have errors it is probably due to column or table names in your database.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources