Script to DB Schema

,

This SP gets all the table names with the column names and data types, datalength. All the existing constrains on the table and column levels along with the Primary and foreign keys and table references

Create                PROC [dbo].[GetTableSchema_All] 

AS
	SET NOCOUNT ON
	
	DECLARE @count int, @tableName varchar(50)

	DECLARE @AllTables TABLE  
	(RowID	 int identity,
	 TableName	varchar(50)
	)

	INSERT @AllTables(TableName)
	SELECT   o.name 
	FROM sysobjects o
	WHERE 	o.xtype = 'U' 
		
	SET @count = @@ROWCOUNT

	DECLARE @table  TABLE   
	(tblID		varchar(50),
	TableName	varchar(100),	
	ColumnName	varchar(50),
 	DataType	varchar(25),
 	ColumnLength	int, 
	ColId		int,
	Caption		varchar(100)
	
	)


	WHILE @count > 0
	BEGIN
		SELECT @tableName = TableName
		FROM @AllTables
		WHERE RowID = @count
		
		INSERT @table(tblID	,
			TableName	,
			ColumnName	,			
 			DataType	,
 			ColumnLength,
			ColId)
		SELECT   o.id, o.name TableName,
			 c.name ColumnName, 
			 t.name,c.length ColumnLength, colid
		FROM sysobjects o
		JOIN syscolumns c
			ON o.id = c.id
		JOIN systypes t
			ON c.xtype = t.xtype				
		
		WHERE 	o.xtype = 'U'
		AND 	o.name = @tableName
		AND	t.name not in('sysname')
		ORDER BY ColumnName

		UPDATE t
		SET Caption  = CONVERT(varchar(100),value)
		FROM @table t
		JOIN (SELECT   objname, value
			FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @tableName, 'column', default)) f			
		ON t.ColumnName = f.objname	


		SET @count = @count - 1
	END	

	SELECT 		tblID		,
			TableName	,
			ColumnName	,			
 			DataType	,
 			ColumnLength	,
			ColId		,
			Caption		
	FROM @table
	ORDER BY TableName, ColId


	
	SELECT TableName, ColumnName, d.ColId, convert(varchar(225),text) Constraints,
		CASE xtype 	WHEN 'C' THEN 'Check' 
				WHEN 'D' THEN 'Default'							
			END ConstraintType,NULL RefTableName, NULL RefColumnName, NULL CnstIsDisabled,NULL CnstIsNotRepl,
				NULL CnstIsDeleteCascade,NULL CnstIsUpdateCascade
	FROM syscomments c 
		JOIN (SELECT  TableName, ColumnName,ColId, id,xtype
			FROM sysobjects o
			JOIN  @table t
			ON parent_obj =  tblID
				AND (o.info = t.ColId)				
			) d
		ON c.id = d.id	

UNION 

	SELECT TableName, NULL, NULL, convert(varchar(250),text ), 'Check Table Level', NULL,NULL,NULL,NULL,NULL,NULL
	FROM syscomments c 
	JOIN (SELECT distinct tblID, TableName, id
			FROM sysobjects o 
			JOIN  @table t
			ON parent_obj =  tblID 
				AND o.info = 0
				AND xtype = 'C'			
			) o
	ON o.id = c.id
UNION
	SELECT TableName, ColumnName, ColId, o.name,'FK',
		CONVERT(varchar(150),'REFERENCES ' + db_name() + '.' + rtrim(user_name(ObjectProperty(rkeyid,'ownerid')))
			+ '.' + object_name(rkeyid))  RefTableName,  
		CONVERT(varchar(50),col_name(rkeyid,k.rkey))  RefColumnName,
		ObjectProperty(k.constid, 'CnstIsDisabled') CnstIsDisabled,
						ObjectProperty(k.constid, 'CnstIsNotRepl') CnstIsNotRepl,
						ObjectProperty(k.constid, 'CnstIsDeleteCascade') CnstIsDeleteCascade,
						ObjectProperty(k.constid, 'CnstIsUpdateCascade') CnstIsUpdateCascade

	FROM @table t
	JOIN dbo.sysforeignkeys k
	ON t.tblID = k.fkeyid
	AND k.fkey = t.ColId
	JOIN dbo.sysobjects  o
	ON t.tblID = o.parent_obj
	AND (k.constid = o.id)
	WHERE xtype = 'F'
UNION	
	SELECT TableName, c.name ColumnName,c.colid, o.name, 'PK',NULL,NULL,NULL,NULL,NULL,NULL
	from sysindexkeys k
	JOIN sysobjects o 
 		ON k.id = o.parent_obj		
		and o.xtype = 'PK'	 	
	JOIN syscolumns c
	on 	c.id = k.id
 		AND 
		c.colid = k.colid
	
	JOIN (SELECT distinct tblID, TableName FROM @table) t
		ON t.tblID = k.id
	WHERE k.indid = 1 
	ORDER BY TableName, ColId

Rate

5 (1)

Share

Share

Rate

5 (1)