Microsoft SQL (MS SQL): Sample SQL Script To Display The Structure Of All Tables In A Database

This sample script is to display the structure of all tables in a database. Before you run these script, you must use (activate) the database that you want to see the structure first.

First part is to create a Store Procedure that we can use to display the structure of a table

-- See Structure Table
-- Save below procedure and run it with parameter: Execute DispStru Table1

CREATE Procedure DispStru
(
@TableName varchar(50)
)

As
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].['+@tableName+']')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
SELECT
cols.name as 'Name',
typs.name as 'Type',
cols.Length,
cols.prec as 'Precision',
cols.Scale,
Allownulls as 'Allow Nulls'
FROM syscolumns cols
INNER JOIN systypes typs ON cols.xusertype=typs.xusertype
WHERE id = OBJECT_ID(@tableName)
ORDER BY name
ELSE
PRINT 'No table named '+@tableName + ' in the ' + db_name() + '
Database'

RETURN

GO

The second part is to create a loop to execute the Stored Procedure for each table in that database

DECLARE @key VARCHAR(255);
DECLARE @cnt int;

DECLARE query_cursor CURSOR FOR
SELECT distinct TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

OPEN query_cursor;

FETCH NEXT FROM query_cursor
INTO @key;

SET @cnt = 0;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Struktur Tabel '+@key;

Execute DispStru @key;

FETCH NEXT FROM query_cursor INTO @key;

SET @cnt = @cnt + 1;

END;

PRINT '(' + convert(varchar, @cnt) + ' Records Affected)';

CLOSE query_cursor;

DEALLOCATE query_cursor;

Article Source: Monx Digital Library

Copyrighted@ Monx Digital Library, otherwise stated
Use of our service is protected by our Terms of Use

Back To Previous Page ...