October 22, 2015 at 7:17 am
Team:
I am almost there. Can someone help me with the syntax so that it shows the length of the varchar column ? Right now it is just showing zero as the size of the varchar column.
select distinct A.name as tab_name , B.name as col_name, C.name as col_type ,
case when C.system_type_id = 167 then '(' + cast(c.precision as varchar(100) ) + ')' end -- 167 = VARCHAR
from sys.tables A
inner join sys.columns B
on ( A.object_id = B.object_id )
inner join sys.types C on ( C.system_type_id = B.system_type_id )
ORDER BY 1,2
--EXTRA CREDIT QUESTION....
--PART 2: I will be really greateful if one of you can add a fourth and a fifth column to this.
4th column should indicate whether the column is a foriegn key. The 5th column should say where it is pointing to ( i mean referencing which table )
October 22, 2015 at 7:34 am
The column you want is max_length not precision.
October 22, 2015 at 7:44 am
For the FK details, left join to sys.foreign_key_columns on the parent values to the sys.columns values
select distinct
A.name as tab_name ,
B.name as col_name,
C.name as col_type ,
case when C.system_type_id in (167,231) then '(' + cast(b.max_length as varchar(100) ) + ')' end, -- 167 = VARCHAR, 231 = NVARCHAR
case when d.parent_object_id is not null then 'FK' else 'Not a FK' end as FK,
object_name(d.referenced_object_id)
from sys.tables A
inner join sys.columns B
on ( A.object_id = B.object_id )
inner join sys.types C on ( C.system_type_id = B.system_type_id )
left join sys.foreign_key_columns D on b.object_id = d.parent_object_id and b.column_id = d.parent_column_id
ORDER BY 1,2
October 22, 2015 at 8:08 am
Ok I need the fourth column as well,
I modified your query works well, but need more info ( last key piece is missing )
select distinct
A.name as tab_name ,
B.name as col_name,
C.name as col_type,
case when C.system_type_id in (167,231) then '(' + cast(b.max_length as varchar(100) ) + ')' else '' end as size , -- 167 = VARCHAR, 231 = NVARCHAR
case when d.parent_object_id is not null then 'FK' else '' end as FK,
case when object_name(d.referenced_object_id) is not null then object_name(d.referenced_object_id) else '' end
as references_this_table,
'' references_this_column
from sys.tables A
inner join sys.columns B
on ( A.object_id = B.object_id )
inner join sys.types C on ( C.system_type_id = B.system_type_id )
left join sys.foreign_key_columns D on b.object_id = d.parent_object_id and b.column_id = d.parent_column_id
ORDER BY 1,2
October 22, 2015 at 8:40 am
Add a second join a second copy of sys.columns and join back on the referenced column data instead.
select distinct
A.name as tab_name ,
B.name as col_name,
C.name as col_type ,
case when C.system_type_id in (167,231) then '(' + cast(b.max_length as varchar(100) ) + ')' end AS StringLength, -- 167 = VARCHAR, 231 = NVARCHAR
case when d.parent_object_id is not null then 'FK' else 'Not a FK' end as FK,
object_name(d.referenced_object_id) AS FKTable,
e.name AS FKColumn
from sys.tables A
inner join sys.columns B
on ( A.object_id = B.object_id )
inner join sys.types C on ( C.system_type_id = B.system_type_id )
left join sys.foreign_key_columns D on b.object_id = d.parent_object_id and b.column_id = d.parent_column_id
left join sys.columns E on d.referenced_object_id = e.object_id and d.referenced_column_id = e.column_id
ORDER BY 1,2
October 22, 2015 at 9:27 am
Yep, works, thanks...
I changed it here and there ...
May I ask whether we could also do a similar query to identified PRIMARY keys ???
select distinct
A.name as tab_name ,
B.name as col_name,
C.name as col_type ,
case when C.system_type_id in (167,231) then '(' + cast(b.max_length as varchar(100) ) + ')' else '' end AS StringLength,
case when b.is_nullable = 0 then 'NOT NULL' else ' ' end as [NOT NULL],
case when d.parent_object_id is not null then 'FK' else ' ' end as FK,
case when object_name(d.referenced_object_id) is not null then object_name(d.referenced_object_id) else '' end AS FKTable,
case when e.name is not null then e.name else '' end AS FKColumn
from sys.tables A
inner join sys.columns B
on ( A.object_id = B.object_id )
inner join sys.types C on ( C.system_type_id = B.system_type_id )
left join sys.foreign_key_columns D on b.object_id = d.parent_object_id and b.column_id = d.parent_column_id
left join sys.columns E on d.referenced_object_id = e.object_id and d.referenced_column_id = e.column_id
ORDER BY 1,2
October 22, 2015 at 10:00 am
mw112009 (10/22/2015)
Yep, works, thanks...I changed it here and there ...
May I ask whether we could also do a similar query to identified PRIMARY keys ???
select distinct
A.name as tab_name ,
B.name as col_name,
C.name as col_type ,
case when C.system_type_id in (167,231) then '(' + cast(b.max_length as varchar(100) ) + ')' else '' end AS StringLength,
case when b.is_nullable = 0 then 'NOT NULL' else ' ' end as [NOT NULL],
case when d.parent_object_id is not null then 'FK' else ' ' end as FK,
case when object_name(d.referenced_object_id) is not null then object_name(d.referenced_object_id) else '' end AS FKTable,
case when e.name is not null then e.name else '' end AS FKColumn
from sys.tables A
inner join sys.columns B
on ( A.object_id = B.object_id )
inner join sys.types C on ( C.system_type_id = B.system_type_id )
left join sys.foreign_key_columns D on b.object_id = d.parent_object_id and b.column_id = d.parent_column_id
left join sys.columns E on d.referenced_object_id = e.object_id and d.referenced_column_id = e.column_id
ORDER BY 1,2
Sure, look at sys.indexes.
One thing, you really should sort by column names not ordinal position. What happens if the columns you are sorting on change ordinal position? Also, if someone new looks at the code it will be obvious how the results are being ordered.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply