September 13, 2012 at 9:54 pm
I am running the following query
select t.name as tablename, c.name as columnname
from sys.table t inner join sys.column c on t.objectid=c.objectid
Now i want to find the datatype of that column what should i do
September 13, 2012 at 10:11 pm
PLS TRY BELOW CODE..
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME
IN(SELECT name FROM sys.tables)
ORDER BY TABLE_NAME
September 13, 2012 at 11:17 pm
subbareddy542 (9/13/2012)
PLS TRY BELOW CODE..SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME
IN(SELECT name FROM sys.tables)
ORDER BY TABLE_NAME
Thanks Subbareddy for the solution. But i am trying to learn SQL Server. So like to know what would be the solution on the line in which i have specified question 🙂 . So after some searcing i have found one more system table as sys.types but it does not have objectid column 🙁 now the problem is that i don't know how to join sys.types with the existing one. Would appreciate your help on this.
September 14, 2012 at 12:28 am
Shadab Shah (9/13/2012)
subbareddy542 (9/13/2012)
PLS TRY BELOW CODE..SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME
IN(SELECT name FROM sys.tables)
ORDER BY TABLE_NAME
Thanks Subbareddy for the solution. But i am trying to learn SQL Server. So like to know what would be the solution on the line in which i have specified question 🙂 . So after some searcing i have found one more system table as sys.types but it does not have objectid column 🙁 now the problem is that i don't know how to join sys.types with the existing one. Would appreciate your help on this.
Hi Shadab,
Try this.
SELECT t.name AS tablename, c.name AS columnname,d.name AS datatype
FROM sys.tables t inner join sys.columns c ON t.object_id=c.object_id
inner join sys.types d ON d.system_type_id = c.system_type_id
To have a clear idea of above quesry please read nested JOIN in sql server also.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
September 14, 2012 at 1:21 am
rhythmk (9/14/2012)
Hi Shadab,
Try this.
SELECT t.name AS tablename, c.name AS columnname,d.name AS datatype
FROM sys.tables t inner join sys.columns c ON t.object_id=c.object_id
inner join sys.types d ON d.system_type_id = c.system_type_id
To have a clear idea of above quesry please read nested JOIN in sql server also.
Thanks rhythmk for the solution. Before i had tried join on schemaid(Of course i was having sys.schema join then) and had also tried user_type_id but that did not work and was also not knowing that system_type_id means numeric representation of datatypes. Once again thanks for the solution
September 14, 2012 at 2:37 am
Shadhab,
The one which you tried joining schema_id, is must in case, if you have the same table name in different schema's with same/different data types
SCENARIO EXPLAINED
a) Suppose you have created the table say "TABLE_NAME" with 30 columns in schema "dbo"
b) In some practical scenario's(like audit purpose), you may want to create a table with the same name "TABLE_NAME" and additional columns (ex.. 35 columns) in different schema (say "Audit")
c) In that case, when you use the above query(without schema info joined), you will have the results with 65 rows (30+35) for table name "TABLE_NAME", which you may not be exactly looking for.
Use the below query (in that case)
--> Replace "TABLE_NAME" with your actual table name
--> Replace "dbo" with your Schema name
SELECT t.name AS tablename, c.name AS columnname,d.name AS datatype
FROM sys.tables t inner join sys.columns c ON t.object_id=c.object_id
inner join sys.types d ON d.system_type_id = c.system_type_id
inner join sys.schemas e ON e.schema_id = t.schema_id
where t.name ='TABLE_NAME' and e.name = 'dbo'
September 14, 2012 at 4:10 am
Thanks bala for the information
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply