May 6, 2008 at 5:54 am
I have a customer who wants to see a definition of all tables, and more precise they want a row for each column in a table.
How can i go through all tables and make an outputfile to e.g. Excel, that show these informations including a table header.
Søren,
MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT
May 6, 2008 at 12:46 pm
Will the create scripts for the table accomplish what you need? That will have the definition of each column in each table.
If so, you can get those by using Enterprise Manager to create the scripts (it's from a right-click on the database).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 3:45 pm
Take a look into
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
You may have to apply some filters to get exactly what you want.
May 6, 2008 at 4:06 pm
Using either MS Enterprise Manager or MS SQL Query Analyzer
Connect to the database you want to document and run the following query.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
You may have to add some additonal filters .... are you familiar with SQL syntax ?
May 7, 2008 at 12:18 pm
/*Requirements BEFORE executing SQL code:
1. Excel workbook must exist on the server in this case C:\TipsNTrick\Tables.xls
2. Excel sheet column headings must be identical for example to those in the SQL select statement:
Table_Name,Column_Name, Default value, Is_Nullable, Data_type,Max Characters
which is the output from the database select statement
3. Sheet name must exist with $ appended for example in excel: [Sheet1] identified in procedure as [Sheet1$].
*/
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;
Database=C:\TipsNTrick\Tables.xls', 'SELECT Table_Name,Column_Name,[Default value],Is_Nullable,Data_type, [Max characters]
FROM [Sheet1$]')
SELECT Table_Name,Column_Name,IsNULL(Column_Default, ' ') AS [Default value],Is_Nullable,Data_type,
IsNULL(Character_Maximum_Length, ' ') AS [Max characters]
FROM Information_Schema.columns
You may have to modify permissions to run distributed queries and can do so using:
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Hope this helps
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply