Styjun

What does "lit." mean in boiling point or melting point specification?

How (un)safe is it to ride barefoot?

What is the "books received" section in journals?

Is it true that "only photographers care about noise"?

Is all-caps blackletter no longer taboo?

What do you call the action of "describing events as they happen" like sports anchors do?

Should I explain the reasons for gaslighting?

Realistic, logical way for men with medieval-era weaponry to compete with much larger and physically stronger foes

What do I need to do, tax-wise, for a sudden windfall?

In The Incredibles 2, why does Screenslaver's name use a pun on something that doesn't exist in the 1950s pastiche?

Was self-modifying code possible using BASIC?

Can I use 220 V outlets on a 15 ampere breaker and wire it up as 110 V?

How does AFV select the winning videos?

What would the consequences be of a high number of solar systems being within close proximity to one another?

What exactly "triggers an additional time" in the interaction between Afterlife and Teysa Karlov?

Why does there seem to be an extreme lack of public trashcans in Taiwan?

Recording Spectral Lines at Home

Does a single fopen introduce TOCTOU vulnerability?

Quasar Redshifts

Why is my power MOSFET heating up when on?

Why did Robert pick unworthy men for the White Cloaks?

What class is best to play when a level behind the rest of the party?

Labels still showing when no Label Features turned on in ArcMap?

Mathematica 12 has gotten worse at solving simple equations?

Getting column information from system MS SQL Server

Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?Check if table exists in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?LEFT JOIN vs. LEFT OUTER JOIN in SQL ServerHow can I get column names from a table in SQL Server?How do I escape a single quote in SQL Server?How do I UPDATE from a SELECT in SQL Server?Find all tables containing column with specified name - MS SQL Server

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

When I am using the below SQL statement to retrieve the column information of a SQL Server database. I am getting more columns than there actually are I presume this is because of the system columns that are there also.

SELECT 
 c.name Field, 
 t.name Type,
 c.Precision, 
 c.Scale,
 c.max_length,
 c.is_nullable,
 c.collation_name 
FROM 
 sys.columns c 
INNER JOIN 
 sys.types t ON t.system_type_id = c.system_type_id
WHERE 
 object_id = OBJECT_ID('SOPOrders') 

You will see the above query is producing ten Order Memos when in fact their should only be the one the var char I still want to be able to report back the prevision dataype maxlength and the null able factor so what is wrong with the above query please.

sql sql-server tsql

share|improve this question

edited Mar 25 at 15:07

Hadi

26.6k73175

asked Mar 24 at 23:11

DaveDave

989

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You have 7 columns in the Select of the query and 7 columns in the query results. I look like the table in the database has a lot more columns and you can display more of the columns by adding then to the Select statement in the query.
  
  – jdweng
  Mar 25 at 0:30
  

  
  
  
  

add a comment | 

1

When I am using the below SQL statement to retrieve the column information of a SQL Server database. I am getting more columns than there actually are I presume this is because of the system columns that are there also.

SELECT 
 c.name Field, 
 t.name Type,
 c.Precision, 
 c.Scale,
 c.max_length,
 c.is_nullable,
 c.collation_name 
FROM 
 sys.columns c 
INNER JOIN 
 sys.types t ON t.system_type_id = c.system_type_id
WHERE 
 object_id = OBJECT_ID('SOPOrders') 

You will see the above query is producing ten Order Memos when in fact their should only be the one the var char I still want to be able to report back the prevision dataype maxlength and the null able factor so what is wrong with the above query please.

sql sql-server tsql

share|improve this question

edited Mar 25 at 15:07

Hadi

26.6k73175

asked Mar 24 at 23:11

DaveDave

989

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You have 7 columns in the Select of the query and 7 columns in the query results. I look like the table in the database has a lot more columns and you can display more of the columns by adding then to the Select statement in the query.
  
  – jdweng
  Mar 25 at 0:30
  

  
  
  
  

add a comment | 

When I am using the below SQL statement to retrieve the column information of a SQL Server database. I am getting more columns than there actually are I presume this is because of the system columns that are there also.

SELECT 
 c.name Field, 
 t.name Type,
 c.Precision, 
 c.Scale,
 c.max_length,
 c.is_nullable,
 c.collation_name 
FROM 
 sys.columns c 
INNER JOIN 
 sys.types t ON t.system_type_id = c.system_type_id
WHERE 
 object_id = OBJECT_ID('SOPOrders') 

You will see the above query is producing ten Order Memos when in fact their should only be the one the var char I still want to be able to report back the prevision dataype maxlength and the null able factor so what is wrong with the above query please.

sql sql-server tsql

share|improve this question

edited Mar 25 at 15:07

Hadi

26.6k73175

asked Mar 24 at 23:11

DaveDave

989

SELECT 
 c.name Field, 
 t.name Type,
 c.Precision, 
 c.Scale,
 c.max_length,
 c.is_nullable,
 c.collation_name 
FROM 
 sys.columns c 
INNER JOIN 
 sys.types t ON t.system_type_id = c.system_type_id
WHERE 
 object_id = OBJECT_ID('SOPOrders') 

sql sql-server tsql

sql sql-server tsql

share|improve this question

edited Mar 25 at 15:07

Hadi

26.6k73175

asked Mar 24 at 23:11

DaveDave

989

share|improve this question

edited Mar 25 at 15:07

Hadi

26.6k73175

asked Mar 24 at 23:11

DaveDave

989

edited Mar 25 at 15:07

Hadi

26.6k73175

edited Mar 25 at 15:07

Hadi

26.6k73175

asked Mar 24 at 23:11

DaveDave

989

asked Mar 24 at 23:11

DaveDave

989

2 Answers
2

active

oldest

votes

2

There may be a couple of reasons for that. First of all, you are joining types by wrong condition - you should use user_type_id instead of system_type_id on both sides. The latter gives you the underlying built-in type which was used as a basis of a user-defined one. See this query, for example:

select * from sys.types t where t.user_type_id != t.system_type_id;

Another possible thing is that the table has a sparse column set, but I might be wrong here.

And, of course, make sure you are actually querying the information about the right table - always include the schema name qualifier, along with the object name, such as:

WHERE object_id=object_id('dbo.SOPOrders')

Failure to do so will not result in duplication you observe, but following it will save you a lot of time trying to figure out the cause of intermittent inconsistencies, when you will have objects with the same name in different schemas.

share|improve this answer

answered Mar 24 at 23:30

Roger WolfRoger Wolf

4,8081918

add a comment | 

1

Why not using Information_Schema.COLUMNS instead??

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'SOPOrders'

Or try to join on

ON c.user_type_id = t.user_type_id

instead of

ON t.system_type_id=c.system_type_id

share|improve this answer

answered Mar 24 at 23:20

HadiHadi

26.6k73175

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55329471%2fgetting-column-information-from-system-ms-sql-server%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

There may be a couple of reasons for that. First of all, you are joining types by wrong condition - you should use user_type_id instead of system_type_id on both sides. The latter gives you the underlying built-in type which was used as a basis of a user-defined one. See this query, for example:

select * from sys.types t where t.user_type_id != t.system_type_id;

Another possible thing is that the table has a sparse column set, but I might be wrong here.

And, of course, make sure you are actually querying the information about the right table - always include the schema name qualifier, along with the object name, such as:

WHERE object_id=object_id('dbo.SOPOrders')

Failure to do so will not result in duplication you observe, but following it will save you a lot of time trying to figure out the cause of intermittent inconsistencies, when you will have objects with the same name in different schemas.

share|improve this answer

answered Mar 24 at 23:30

Roger WolfRoger Wolf

4,8081918

add a comment | 

2

There may be a couple of reasons for that. First of all, you are joining types by wrong condition - you should use user_type_id instead of system_type_id on both sides. The latter gives you the underlying built-in type which was used as a basis of a user-defined one. See this query, for example:

select * from sys.types t where t.user_type_id != t.system_type_id;

Another possible thing is that the table has a sparse column set, but I might be wrong here.

And, of course, make sure you are actually querying the information about the right table - always include the schema name qualifier, along with the object name, such as:

WHERE object_id=object_id('dbo.SOPOrders')

Failure to do so will not result in duplication you observe, but following it will save you a lot of time trying to figure out the cause of intermittent inconsistencies, when you will have objects with the same name in different schemas.

share|improve this answer

answered Mar 24 at 23:30

Roger WolfRoger Wolf

4,8081918

add a comment | 

There may be a couple of reasons for that. First of all, you are joining types by wrong condition - you should use user_type_id instead of system_type_id on both sides. The latter gives you the underlying built-in type which was used as a basis of a user-defined one. See this query, for example:

select * from sys.types t where t.user_type_id != t.system_type_id;

Another possible thing is that the table has a sparse column set, but I might be wrong here.

And, of course, make sure you are actually querying the information about the right table - always include the schema name qualifier, along with the object name, such as:

WHERE object_id=object_id('dbo.SOPOrders')

Failure to do so will not result in duplication you observe, but following it will save you a lot of time trying to figure out the cause of intermittent inconsistencies, when you will have objects with the same name in different schemas.

share|improve this answer

answered Mar 24 at 23:30

Roger WolfRoger Wolf

4,8081918

select * from sys.types t where t.user_type_id != t.system_type_id;

WHERE object_id=object_id('dbo.SOPOrders')

share|improve this answer

answered Mar 24 at 23:30

Roger WolfRoger Wolf

4,8081918

answered Mar 24 at 23:30

Roger WolfRoger Wolf

4,8081918

answered Mar 24 at 23:30

Roger WolfRoger Wolf

4,8081918

1

Why not using Information_Schema.COLUMNS instead??

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'SOPOrders'

Or try to join on

ON c.user_type_id = t.user_type_id

instead of

ON t.system_type_id=c.system_type_id

share|improve this answer

answered Mar 24 at 23:20

HadiHadi

26.6k73175

add a comment | 

1

Why not using Information_Schema.COLUMNS instead??

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'SOPOrders'

Or try to join on

ON c.user_type_id = t.user_type_id

instead of

ON t.system_type_id=c.system_type_id

share|improve this answer

answered Mar 24 at 23:20

HadiHadi

26.6k73175

add a comment | 

Why not using Information_Schema.COLUMNS instead??

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'SOPOrders'

Or try to join on

ON c.user_type_id = t.user_type_id

instead of

ON t.system_type_id=c.system_type_id

share|improve this answer

answered Mar 24 at 23:20

HadiHadi

26.6k73175

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'SOPOrders'

ON c.user_type_id = t.user_type_id

ON t.system_type_id=c.system_type_id

share|improve this answer

answered Mar 24 at 23:20

HadiHadi

26.6k73175

answered Mar 24 at 23:20

HadiHadi

26.6k73175

answered Mar 24 at 23:20

HadiHadi

26.6k73175