June 19, 2003 at 9:21 am
Hi,
Is there a way to display a table's properties using TSQL in QA?
Many thanks. Jeff
For example, in Oracle, the command is "descr or describe":
SQL> descr emp
...
Name Null? Type
------------------- -------- ----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Many thanks. Jeff
June 19, 2003 at 9:39 am
June 19, 2003 at 9:47 am
select * from information_schema.columns where table_name = 'your table'
June 20, 2003 at 2:03 pm
Usually I just want columns to set up my insert queries so I use:
select sc.name from sysobjects so, syscolumns sc where
so.id = sc.id
and upper(so.name) = 'tablename' order by sc.colid;
If you want the other info:
select sc.name,st.name, sc.prec, sc.scale, sc.length
from sysobjects so, syscolumns sc, systypes st where
so.id = sc.id and
sc.type = st.type
and upper(so.name) = 'tablename' order by sc.colid
June 23, 2003 at 8:49 am
You can highlight the table name in QA and then press Alt, F1. Make sure you are still holding down Alt when you click F1.
Hi,
Is there a way to display a table's properties using TSQL in QA?
Many thanks. Jeff
For example, in Oracle, the command is "descr or describe":
SQL> descr emp
...
Name Null? Type
------------------- -------- ----
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
[/quote]
June 26, 2003 at 8:44 am
Executing the stored procedure sp_help with no parameters will return a list of tables and objects in the database. Running sp_help followed by by the table name will return schema info about that table; for example, "exec sp_help myTable"...
June 26, 2003 at 8:57 am
Great. Thanks.
Is there in TSQL to display all the objects (tables, views) for a given schema/user ie: in Oracle, SELECT * FROM CAT/CATALOGUE will only display all the objects that this particular user has access to (owns or has been granted to by another user)
Many thanks again. Jeff
quote:
Executing the stored procedure sp_help with no parameters will return a list of tables and objects in the database. Running sp_help followed by by the table name will return schema info about that table; for example, "exec sp_help myTable"...
Many thanks. Jeff
June 26, 2003 at 1:42 pm
This is somewhat rough, but I think addresses your last question.
SELECTsyob.name 'ObjectName',
--syco.name 'ColumnName',
--syco.type,
syob.type 'ObjectType',
syus.name 'UserName'
FROMsysobjects syob
JOIN
sysusers syus
ON syob.uid = syus.uid
/* If you want column data
LEFT OUTER JOIN
syscolumns syco
ON syob.id = syco.id
*/
WHEREsyus.name = '<username>'
Regards,
SJTerrill
Edited by - TheWildHun on 06/26/2003 1:43:30 PM
June 30, 2003 at 12:15 pm
You can use my stored proc, sp_tableinfo, to get a concise listing of most of a table's important properties:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply