August 21, 2003 at 10:28 am
I need to create a report that lists all tables with their respective columns and primary key, foreign key, index information. Please see the following query which is giving me an error on no macth for the column prefix. Any help would be appreciated.
select t.[name] as tablename, c.[name] as columnname, isnull(p.[name],'''') as ConstraintName,
case p.xtype
When 'PK' Then 'Primary Key'
When 'C' Then 'Check'
When 'UQ' Then 'Unique'
When 'F' Then 'Foreign Key'
When 'TR' Then 'Trigger'
When 'D' Then 'Default'
Else ''''
End As ConstraintType
from sysobjects t, syscolumns c
left join sysobjects p on t.[id] = p.parent_obj
where (t.xtype='U') and (c.xtype ='U') and (t.id = c.id)
order by tablename, columnname, ConstraintType asc
Charles L. Nichols
Charles L. Nichols
August 21, 2003 at 11:38 am
I changed your query a little.
select t.name as tablename
, c.name as columnname
, isnull(p.name,'''') as ConstraintName,
case p.xtype
When 'PK' Then 'Primary Key'
When 'C' Then 'Check'
When 'UQ' Then 'Unique'
When 'F' Then 'Foreign Key'
When 'TR' Then 'Trigger'
When 'D' Then 'Default'
Else ''
End As ConstraintType
from sysobjects t
join syscolumns c
on t.id = c.id
left join sysobjects p
on t.id = p.parent_obj
where (t.xtype='U')
--and (c.xtype ='U')
order by tablename, columnname, ConstraintType asc
Not 100% this is what you want but it works.
Tom
August 21, 2003 at 11:51 am
Here is the modified script, it works. I have changed the joins a bit and took out the c.xtype = 'U' clause. Syscolumns.xtype is a numeric value indicating the datatype not like sysobjects t.xtype which is a type of object with letters.
Hope this is producing the results you are looking for.
*******************************************
select t.name tablename, c.name as columnname, isnull(p.name,'''') as ConstraintName,
case p.xtype
When 'PK' Then 'Primary Key'
When 'C' Then 'Check'
When 'UQ' Then 'Unique'
When 'F' Then 'Foreign Key'
When 'TR' Then 'Trigger'
When 'D' Then 'Default'
Else ''''
End As ConstraintType
from sysobjects t
left join sysobjects p on t.id = p.parent_obj
join syscolumns c on t.id = c.id
where t.xtype='U'
--- and c.xtype ='U'
order by tablename, columnname, ConstraintType asc
********************************************
August 21, 2003 at 12:06 pm
Charles,
You have mised two different methods of joins. You start out using the old method joins where you list the tables in the FROM clause and make the join in the WHERE clause. (from sysobjects t, syscolumns c). The next line, you use the modern method of setting up a join (left join sysobjects p on t.[id] = p.parent_obj). Unfortunatley, you can not mix these two methods, you either have to do one method or the other. I would recommend changing you FROM and WHERE clauses to be as follows:
from sysobjects t inner join syscolumns c on t.id = c.id
left join sysobjects p on t.[id] = p.parent_obj
where (t.xtype='U') and (c.xtype ='U')
Also, I believe the (c.xtype ='U') in the WHERE clause will cause an error because this field is tinyint and you are searching for a character. Unfortunatley, I do not know the breakdown of the values and what they represent for the xtype column in SYSCOLUMNS
August 22, 2003 at 5:32 am
Thanks to everyone who replied and offered help with creating and modifying the query. I just began supporting SQL Server, coming from DB2 mainframe DBA.
Thanks again for all the help
Charles L. Nichols
Charles L. Nichols
August 22, 2003 at 7:23 am
Welcome to the club and good luck with SQL Server. You may find less complicated and easier to manage than a mainframe
-Sravan
August 22, 2003 at 1:13 pm
That is a really sweet query...didn't realize it was as easy as joining on parent_obj.
Someone should post this...cnichol4?
select t.name as tablename
, c.name as columnname
, isnull(p.name,'''') as ConstraintName,
case p.xtype
When 'PK' Then 'Primary Key'
When 'C' Then 'Check'
When 'UQ' Then 'Unique'
When 'F' Then 'Foreign Key'
When 'TR' Then 'Trigger'
When 'D' Then 'Default'
Else ''
End As ConstraintType
from sysobjects t
inner join syscolumns c on t.id = c.id
left join sysobjects p on t.[id] = p.parent_obj
where (t.xtype='U')
order by tablename, columnname, ConstraintType asc
Signature is NULL
August 22, 2003 at 3:26 pm
Charles,
Unfortunately, simply adding the column list to this report is not enough. By joining them as above there are two distinct reports contained in one result set, ie. the columns listing is unrelated to the constraint listing.
This should really be two seperate queries:
--List Table and Columns
Select t.[name] As tablename, c.[name] As columnname--, st.[name] As DataType, c.[length] As DataSize
From sysobjects t
Inner Join syscolumns c on t.[id] = c.[id]
--Left Join systypes st on c.xtype = st.xtype
Where t.xtype = 'U'
Order By TableName, columnname
--List Tables and Constraints
select t.[name] as tablename, isnull(p.[name],'') as ConstraintName,
case p.xtype
When 'PK' Then 'Primary Key'
When 'C' Then 'Check'
When 'UQ' Then 'Unique'
When 'F' Then 'Foreign Key'
When 'TR' Then 'Trigger'
When 'D' Then 'Default'
Else ''
End As ConstraintType
from sysobjects t
left join sysobjects p on t.[id] = p.parent_obj
where t.xtype='U'
order by tablename asc, ConstraintType Desc
If you want to list the columns that referenced by the individual constraints, you will need to make your way through several system tables and my brain gets fuzzy once I start looking at sysindexes and sysindexkeys. 🙂
To get a better idea, get the id of a table you are interested in and use it in the following queries:
Declare @objID int
Set @objID = [your object id here]
select * from syscolumns where [id] = @objID
select * from sysobjects where [id] = @objID
select * from sysconstraints where [id] = @objID
select * from sysobjects where [id] = @objID
select * from sysobjects where [parent_obj] = @objID
select * from sysindexes where [id] = @objID
select * from sysindexkeys where [id] = @objID
Dan B
August 25, 2003 at 1:36 am
Hi!
This will solve your problem!!!
select a.id as Tableid,a.Name as TableName,b.name as colName,b.colid as colId,
d.name as constraintname,d.xtype as constrainttype , e.name
from ((((sysobjects a inner join syscolumns b
on a.id=b.id) left outer join sysconstraints c on c.id=b.id and c.colid=b.colid-1 )
left outer join sysobjects d on d.parent_obj=a.id and c.constid=d.id) left outer join
sysobjects e on e.parent_obj=a.id and e.xtype='TR')
where a.xtype= 'U'
order by 1, 2, b.colid
Regards
mandar K
August 25, 2003 at 9:37 am
Nice!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply