Silly question maybe? - 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 2000 Forums

Transact-SQL (2000)

Silly question maybe?

Author

Topic

Crespo24
Village Idiot

144 Posts

Posted - 2002-12-17 : 08:26:42

Salute,

I want to write some sort of stored procedure that would take say, a table name and query every column in the table and return the DISTINCT values for each column in the table (which we pass on to the SP).

Any suggestions?

-------------------------

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2002-12-17 : 08:38:07

Hi!

Try this

declare @TableName varchar(50)
declare @str varchar(1000)

set @TableName = 'Customers'
set @str = ''
select @str = @str + 'SELECT DISTINCT ' + name + ' FROM ' + @TableName + '; ' from syscolumns where id = object_id(@TableName)
EXEC(@str)

Now this should return a resultset of distinct values FOR EACH column in the table. Some of the columns didnt make sense to be selected DISTINCT, like CustomerID for example, coz all the results are unique anyway.

OS

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-12-17 : 08:48:12

the following link may help........

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14993

especially the last reply in the example....

you're going to need to mix information_schema.views and dynamic sql....I think somebody has already written an article on this....but at the moment i can't find it....(since it's your problem.....maybe you can afford to go through the "forum search" results more than me!!!....(use schema dynamic, "option all words") as a starting point for the search)

Edited by - AndrewMurphy on 12/17/2002 08:48:33

Crespo24
Village Idiot

144 Posts

Posted - 2002-12-17 : 08:51:12

Hi.... thanks for both your replies...
this is what I have so far... and I think I am on the right tracks...
I will also check your link...

CREATE PROCEDURE CHECKTABLE(@TABLENAME VARCHAR(20))
AS
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TABLENAME)
BEGIN
PRINT @TABLENAME +' ALREADY EXISTS ON DATABASE... PLEASE RENAME YOU TABLE.'
DECLARE @COUNTER INTEGER
DECLARE @LOOP_COUNTER INTEGER
SET @COUNTER = 0
SET @LOOP_COUNTER = 0

SET @COUNTER = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLENAME)
WHILE (@LOOP_COUNTER <= @COUNTER)
BEGIN
SELECT DISTINCT A.COLUMN_NAME
FROM @TABLENAME

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME
AND ORDINAL_POSITION = @LOOP_COUNTER
SET @LOOP_COUNTER = @LOOP_COUNTER + 1
END
END
ELSE
BEGIN
PRINT @TABLENAME + ' DOES NOT EXIST ON THIS DATABASE...'
END
PRINT ''
PRINT 'PLEASE WAIT WHILE SQL-SERVER GENERATES A LIST OF DISTINCT VALUES FOR ALL THE COLUMNS...'

-------------------------

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-17 : 09:13:57

do you just want to print a list of the columns values here?
SELECT DISTINCT A.COLUMN_NAME FROM @TABLENAME
won't work.

declare @colname varchar(128)
select @colname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME
AND ORDINAL_POSITION = @LOOP_COUNTER

declare @sql varchar(1000)
select @sql = 'SELECT DISTINCT ' + @colname + 'from ' + @TABLENAME
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Crespo24
Village Idiot

144 Posts

Posted - 2002-12-17 : 09:16:35

DROP PROCEDURE CHECKTABLE

CREATE PROCEDURE CHECKTABLE(@TABLENAME VARCHAR(20))
AS
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TABLENAME)
BEGIN
PRINT @TABLENAME +' ALREADY EXISTS ON DATABASE... PLEASE RENAME YOU TABLE.'

DECLARE @COUNTER INTEGER
DECLARE @LOOP_COUNTER INTEGER
DECLARE @THE_NAME VARCHAR(30)
DECLARE @SQL_STETMENT VARCHAR(100)
SET @COUNTER = 0
SET @LOOP_COUNTER = 0

SET @COUNTER = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLENAME)

WHILE (@LOOP_COUNTER <= @COUNTER)
BEGIN

SET @THE_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME
AND ORDINAL_POSITION = @LOOP_COUNTER)

SET @SQL_STETMENT = 'SELECT DISTINCT ' + @THE_NAME+ ' FROM ' + @TABLENAME
EXEC (@SQL_STETMENT)

SET @LOOP_COUNTER = @LOOP_COUNTER + 1
END
END
ELSE
BEGIN
PRINT @TABLENAME + ' DOES NOT EXIST ON THIS DATABASE...'
END
PRINT ''
PRINT 'PLEASE WAIT WHILE SQL-SERVER GENERATES A LIST OF DISTINCT VALUES FOR ALL THE COLUMNS...'

This is the working version.... even though I did not check the links properly.. it was nice of you to reply.

Thanks.

-------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources