September 14, 2015 at 11:08 am
Select A.name, B.name
FROM
SYS.tables A
inner join
SYS.columns B on ( A.object_id = B.object_id )
ORDER BY 1 , 2
What I need is a 3rd column which shows the type of the column ( varchar() or int or ... )
Ideal if you can even get it to show the length of the column ( Ex: varchar(100), char(10), etc )
September 14, 2015 at 11:38 am
September 14, 2015 at 12:04 pm
Ed Wagner (9/14/2015)
Duplicate post. Direct replies to http://www.sqlservercentral.com/Forums/Topic1719361-2799-1.aspx
Although similar the other post asks about information regarding Foreign Keys while this thread asks for Columns Data Type and Size. Now whether the OP meant one thing and then corrected himself...who knows.
September 14, 2015 at 12:06 pm
This is not a duplicate post.
This is separate from the one where I asked for fks
September 14, 2015 at 12:36 pm
When I'm looking for that kind of info I just normally write a query something like this.
USE AdventureWorks2012
SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
NUMERIC_PRECISION,
NUMERIC_SCALE
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Address'
AND TABLE_SCHEMA = 'Person'
September 14, 2015 at 12:39 pm
Thnaks This works...
Hoewever can we limit it to showing only tables please. I see that It also lists the views.
SELECT
TABLE_SCHEMA, TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
NUMERIC_PRECISION,
NUMERIC_SCALE
FROM
INFORMATION_SCHEMA.COLUMNS
September 14, 2015 at 12:42 pm
Sorry I figured out
SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
NUMERIC_PRECISION,
NUMERIC_SCALE, *
FROM
INFORMATION_SCHEMA.COLUMNS A
inner join SYS.tables B
on ( A.TABLE_NAME = B.name )
September 14, 2015 at 12:54 pm
mw112009 (9/14/2015)
Select A.name, B.name
FROM
SYS.tables A
inner join
SYS.columns B on ( A.object_id = B.object_id )
ORDER BY 1 , 2
What I need is a 3rd column which shows the type of the column ( varchar() or int or ... )
Ideal if you can even get it to show the length of the column ( Ex: varchar(100), char(10), etc )
You should avoid ordering by ordinal position of columns. This is a very bad habit and one that can cause extremely difficult bugs in systems. You should use the column name.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2015 at 1:17 pm
yb751 (9/14/2015)
Ed Wagner (9/14/2015)
Duplicate post. Direct replies to http://www.sqlservercentral.com/Forums/Topic1719361-2799-1.aspxAlthough similar the other post asks about information regarding Foreign Keys while this thread asks for Columns Data Type and Size. Now whether the OP meant one thing and then corrected himself...who knows.
You're absolutely right. I saw the same query and replied without reading the question. My apologies.
September 14, 2015 at 1:47 pm
Here is a start using the system views. You would need to do some work to get things like VARCHAR(100), VARCHAR(MAX), DECIMAL(18,6), etc.
select
tab.name tableName,
tab.object_id ObjectId,
col.name ColumnName,
col.column_id ColumnId,
typ.name TypeName,
col.system_type_id SystemTypeId,
col.user_type_id UserTypeId,
col.max_length MaxLength,
col.precision [Precision],
col.scale [Scale],
col.collation_name CollationName,
col.is_nullable IsNullable,
col.is_ansi_padded IsAnsiPadded,
col.is_rowguidcol IsRowGuidCol,
col.is_identity IsIdentity,
col.is_computed IsComputed,
col.default_object_id DefaultObjectId
from
sys.tables tab
inner join sys.columns col
on (tab.object_id = col.object_id)
inner join sys.types typ
on (col.system_type_id = typ.system_type_id and
col.user_type_id = typ.user_type_id)
order by
tab.name,
col.column_id
September 14, 2015 at 2:17 pm
A simple CASE statement can 'beautify' the Data Type but just be mindful you'd have to account for quite a few different types.
Here is an example of a few:
USE AdventureWorks2012
SELECT
COLUMN_NAME,
--DATA_TYPE,
CASE DATA_TYPE
WHEN 'NVARCHAR' THEN 'NVARCHAR(' + CONVERT(NVARCHAR(100),CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN 'DECIMAL' THEN 'DECIMAL(' + CONVERT(NVARCHAR(100),NUMERIC_PRECISION) + ',' + CONVERT(NVARCHAR(100),NUMERIC_SCALE) + ')'
WHEN 'NUMERIC' THEN 'NUMERIC(' + CONVERT(NVARCHAR(100),NUMERIC_PRECISION) + ',' + CONVERT(NVARCHAR(100),NUMERIC_SCALE) + ')'
ELSE UPPER(DATA_TYPE) END AS NiceDataType
FROM
INFORMATION_SCHEMA.COLUMNS
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply