nvarchar datatype filed repeated

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

Development Tools

Other Development Tools

nvarchar datatype filed repeated

Author

Topic

Mamatha
Posting Yak Master

102 Posts

Posted - 2005-01-28 : 01:59:55

Hi

I tried to retrieve the structure of SQL Server table,but if one of the field contains nvarchar then the field name is repeating with the sysname datatype.For example one table contains filelds and datatyps like that:

a text
b varchar
c nvarchar

while i am trying to retrieve the table structure then it will displys output like that:

a text
b varchar
c nvarchar
c sysname
Why the field is repeating?Please give the solution to avoid from repetation,thanks in advance.

Mamatha

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-28 : 15:28:56

Please post your code. We can't see what you are doing, so we can't help until we see the code.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-29 : 00:24:39

They're joining to the systypes table to get the information they're looking for. If you look at that table though, there is exactly ONE xtype with multiple values. It's sysname. The sysname is basically an NVARCHAR field. You will need to add a WHERE clause to your query and filter out WHERE systypes.name <> 'sysname' for your query to be correct. See the following results:



SELECT 
	st1.name, 
	st1.xtype,
	st2.counter
FROM 
	systypes st1
	INNER JOIN (
		SELECT xtype, count(*) AS counter
		FROM systypes
		GROUP BY xtype
		HAVING COUNT(*)>1) st2 ON st1.xtype = st2.xtype

NOTE: You shouldn't be looking directly at the system tables at all. If you use the INFORMATION_SCHEMA views, you wouldn't get the duplicate issue you're now facing. Run the following:


SELECT DISTINCT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
ORDER BY DATA_TYPE

As you see, there are no duplicate data types in this table, because sysname isn't represented seperately.

If you CREATE a table with sysname or VARCHAR and query systypes, it will show up as both nvarchar and sysname. If you query the views, it will only show up as nvarchar.



USE Northwind
GO

CREATE TABLE test_sysname(test sysname)
GO

SELECT DISTINCT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
GO

SELECT 
	TABLE_NAME,
	COLUMN_NAME,
	DATA_TYPE
FROM 
	INFORMATION_SCHEMA.COLUMNS 
WHERE 
	TABLE_NAME = 'test_sysname'

GO

DROP TABLE test_sysname
GO

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources