Before I write one line of code in a new SQL Server database, I run the following script. What this script does is read the sysobject and syscolumns tables, and creates a table with all of the tables'
DECLARE @TABLENAME VARCHAR(100) DECLARE SOURCEMAP INSENSITIVE CURSOR FOR SELECT Name FROM sysobjects WHERE type = "U" ORDER BY name FOR READ ONLY --Check to see if the table is present; if not, let's create it. IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.TablesColumns')) BEGIN CREATE TABLE TablesColumns(ColumnName VARCHAR(100),ColumnType VARCHAR(30),TableName VARCHAR(100),ColumnSize INTEGER) END OPEN SOURCEMAP --Loop thru our cursor and insert our history and stage tale WHILE ( 1 = 1 ) BEGIN FETCH NEXT FROM SOURCEMAP INTO @TABLENAME /*Bail out when all records have been inserted*/
IF (@@Fetch_Status <> 0) BREAK ELSE BEGIN /*Perform our select and insert*/ INSERT INTO TablesColumns (ColumnName, ColumnType,TableName,ColumnSize) SELECT SC.Name, CASE SC.XTYPE WHEN 189 THEN 'BINARY' WHEN 173 THEN 'BINARY' WHEN 52 THEN 'SMALLINT' WHEN 56 THEN 'INTEGER' WHEN 167 THEN 'VARCHAR' + '(' + CONVERT(VARCHAR,sc.Length) + ')' WHEN 175 THEN 'CHAR' + '(' + CONVERT(VARCHAR,sc.Length) + ')' WHEN 62 THEN 'FLOAT' WHEN 59 THEN 'REAL' WHEN 104 THEN 'BIT' WHEN 48 THEN 'TINYINT' WHEN 35 THEN 'TEXT' WHEN 99 THEN 'NTEXT' WHEN 61 THEN 'DATETIME' WHEN 60 THEN 'MONEY' WHEN 106 THEN 'DECIMAL' WHEN 108 THEN 'NUMERIC' WHEN 34 THEN 'IMAGE' WHEN 58 THEN 'SMALLDATETIME' WHEN 231 THEN 'NVARCHAR' + '(' + convert(varchar,sc.Length) + ')' END AS ColumnType, @TABLENAME AS [TableName], sc.Length AS [ColumnSize] FROM SYSCOLUMNS SC,SYSOBJECTS SO WHERE SO.ID = SC.ID AND SO.NAME =@TABLENAME /*If insert failed then raise error and rollback*/ END END Close SOURCEMAP DeAllocate SOURCEMAP
For More Information
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL or MS SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL and MS SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL and MS SQL Server gurus are waiting to answer your technical questions.
This was first published in February 2005