Today I had a request to produce a report showing all the columns for the tables within a database. A interesting request and one that I was sure could be done, however it was the first time I had to do it. A bit of investigation led me to the nifty stored procedure.
EXEC sp_help [TableName]
This produced a great result but I thought it would be great if I could do this automatically for all the tables in the database. This is what I came up with.
DECLARE TablesCursor CURSOR FOR SELECT table_name AS Name FROM INFORMATION_SCHEMA.Tables; OPEN TablesCursor DECLARE @TableName sysname; FETCH NEXT FROM TablesCursor INTO @TableName; WHILE (@@FETCH_STATUS <> -1) BEGIN; EXEC sp_help @TableName FETCH NEXT FROM TablesCursor INTO @TableName; END; CLOSE TablesCursor; DEALLOCATE TablesCursor;
While the output is good I needed a simple report that I could export to excel. So I decided to try another approach.
SELECT table_name, column_name, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_precision_radix, numeric_scale FROM INFORMATION_SCHEMA.COLUMNS ORDER BY table_name, ordinal_position
Very simple. Now rather than a fancy export I just copy and pasted!
hi,When i execute the third(ALTER TABLE tabamnlee ALTER COLUMN id SET NOT NULL), i meet erro,like this:[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token NOT was found following alter column id set . Expected tokens may include: DEFAULT . SQLSTATE=42601Statement processed with ERROR. My DB is DB2 V8.3BTW,ALTER TABLE tabamnlee ADD COLUMN id INTEGERUPDATE tabamnlee SET id = 0 make that two become one,like this:ALTER TABLE tabamnlee ADD COLUMN id INTEGER NOT NULL with default 0;Thank you for your reply.Glee
The code above works with SQL Server 2005. However, for SQL Server 2000, you will need to use the code below:SELECT O.name AS TableName, C.name AS ColumnName, T.name AS Type, C.length AS MaxLengthFROM sysobjects OINNER JOIN symsolucns C ON O.id = C.idINNER JOIN systypes T ON C.xtype = T.xtypeWHERE O.type = U'ORDER BY O.name, C.colid