Help with query - New to SQL

Not sure how to quite ask this question. I know how to do what I want under MySQL but with MS SQL I'm mystified. The main issue is that the database I'm attempting to access is fairly large so my query times out also that I lack any real access to the server itself except via my workstation. What I'd like to do is try to map the structure of a table in the database so that I can see what fields the front end is current not utilizing. The basic setup is a webserver and access the database via PHP. I'm hoping that this is a simple thing to do but I haven't found a solution yet anywhere online.

<?php
require ("connect.inc");

$db = @.MSSQL_CONNECT($hostname,$username,$password) or DIE("DATABASE FAILED TO RESPOND.");
mssql_select_db($dbName) or DIE("Table unavailable");

$result = mssql_query("SELECT * FROM test");
print("<table border=1>");
print("<tr><th>Name</th></tr>");
for ($i=0; $i < mssql_num_fields($result); $i++) {
print("<tr>");
printf("<td>%s</td>", mssql_field_name($result, $i));
print("</tr>");
}
print("</table>");
?>

Most people know exactly how their database is structured but since I'm working on an existing one that does not have any documentation I'd like to take the time to map it out somewhat even if its just for my own peace of mind.

Thanks in advance for your attention and advice.There is a management view for displaying the columns, its INFORMATION_SCHEMA.Columns, try the following:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SomeTableName'

HTH, Jens Suessmeyer.

http://wwwsqlserver2005.de|||Hi,

First of all thanks for your response. I forgot to mention that I did try the above mentioned management view and failed. Maybe you could help me figure out exactly why it didn't work the way it should have.

$result = mssql_query("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table'");
print("<table border=1>");
print("<tr><th>Name</th></tr>");
for ($i=0; $i < mssql_num_fields($result); $i++) {
print("<tr>");
printf("<td>%s</td>", mssql_field_name($result, $i));
print("</tr>");
}
print("</table>");

-- Query Returned

Warning: mssql_query() [function.mssql-query]: message: Invalid object name 'INFORMATION_SCHEMA.COLUMNS'. (severity 16) in /var/www/html/TEST/dennis/dennis on line 12

Warning: mssql_query() [function.mssql-query]: Query failed in /var/www/html/TEST/dennis/dennis on line 12

Warning: mssql_num_fields(): supplied argument is not a valid MS SQL-result resource in /var/www/html/TEST/dennis/dennis on line 15
Name
Once again thanks.|||Which version of SQL Server are you using ? The Information_SCHEMA should be there.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||The version I'm told is 6.5 but migrating to 2005 very soon.|||6.5 and migrating to 2005 very soon.|||

OK, then you might have a look on the View text from of the INFORMATION_SCHEMA.COLUMNS:

-- Displays columns accessable to the current user
CREATE VIEW INFORMATION_SCHEMA.COLUMNS
AS
SELECT
db_name() AS TABLE_CATALOG,
schema_name(o.schema_id) AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
c.name AS COLUMN_NAME,
ColumnProperty(c.object_id, c.name, 'ordinal') AS ORDINAL_POSITION,
convert(nvarchar(4000),
object_definition(c.default_object_id)) AS COLUMN_DEFAULT,
convert(varchar(3), CASE c.is_nullable
WHEN 1 THEN 'YES' ELSE 'NO' END) AS IS_NULLABLE,
type_name(c.system_type_id) AS DATA_TYPE,
ColumnProperty(c.object_id, c.name, 'charmaxlen') AS CHARACTER_MAXIMUM_LENGTH,
ColumnProperty(c.object_id, c.name, 'octetmaxlen') AS CHARACTER_OCTET_LENGTH,
convert(tinyint, CASE -- int/decimal/numeric/real/float/money
WHEN c.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN c.precision
END) AS NUMERIC_PRECISION,
convert(smallint, CASE -- int/money/decimal/numeric
WHEN c.system_type_id IN (48, 52, 56, 60, 106, 108, 122, 127) THEN 10
WHEN c.system_type_id IN (59, 62) THEN 2 END) AS NUMERIC_PRECISION_RADIX, -- real/float
convert(int, CASE -- datetime/smalldatetime
WHEN c.system_type_id IN (58, 61) THEN NULL
ELSE odbcscale(c.system_type_id, c.scale) END) AS NUMERIC_SCALE,
convert(smallint, CASE -- datetime/smalldatetime
WHEN c.system_type_id IN (58, 61) THEN 3 END) AS DATETIME_PRECISION,
convert(sysname, null) AS CHARACTER_SET_CATALOG,
convert(sysname, null) AS CHARACTER_SET_SCHEMA,
convert(sysname, CASE
WHEN c.system_type_id IN (35, 167, 175) -- char/varchar/text
THEN CollationProperty(c.collation_name, 'sqlcharsetname')
WHEN c.system_type_id IN (99, 231, 239) -- nchar/nvarchar/ntext
THEN N'UNICODE'
END) AS CHARACTER_SET_NAME,
convert(sysname, null) AS COLLATION_CATALOG,
convert(sysname, null) AS COLLATION_SCHEMA,
c.collation_name AS COLLATION_NAME,
convert(sysname, CASE WHEN c.user_type_id > 256
THEN db_name() END) AS DOMAIN_CATALOG,
convert(sysname, CASE WHEN c.user_type_id > 256
THEN schema_name(o.schema_id)
END) AS DOMAIN_SCHEMA,
convert(sysname, CASE WHEN c.user_type_id > 256
THEN type_name(c.user_type_id)
END) AS DOMAIN_NAME
FROM
sys.objects o JOIN sys.columns c ON c.object_id = o.object_id
WHERE
o.type IN ('U', 'V')

I don′t know if all functions apply to SQL 6.5, but you can have a look on the sources in the meantime.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Wouldn't EXECUTE sp_help 'someTableName' also get some or most of the required info?

/Kenneth

|||Thanks for the help. With your help and other co-workers I've managed to get the information I needed.

Thanks again!