modelo-de-tabelas.sql

	SELECT '<TABELAS>' +
	(SELECT
		TABELA.NAME AS 'NOME'
		, COLUNA.NAME AS 'NOME'
		, CASE WHEN COLUNA.PRECISION > 0 THEN COLUNA.PRECISION ELSE COLUNA.MAX_LENGTH END AS 'TAMANHO'
		, COLUNA.scale AS 'ESCALA'
		, CASE COLUNA.IS_NULLABLE WHEN 1 THEN 'N' ELSE 'S' END AS 'OBRIGATORIO'
		, CASE WHEN (SELECT COUNT(*)
				FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C
				JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K
				ON C.TABLE_NAME = K.TABLE_NAME
				AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
				AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
				AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME
				WHERE C.CONSTRAINT_TYPE = 'PRIMARY KEY'
				AND K.COLUMN_NAME = COLUNA.name
				AND C.TABLE_NAME = TABELA.name)
			 > 0 THEN 'S' ELSE 'N' END AS 'CHAVE_PRIMARIA'
		, CASE WHEN COLUNA.IS_IDENTITY = 1 THEN 'N' ELSE 'S' END AS 'EDITAVEL'
		, '' AS 'VALOR'
		, TIPO.NAME AS 'TIPO'
	FROM SYS.OBJECTS AS TABELA WITH (NOLOCK)
	JOIN SYS.COLUMNS AS COLUNA WITH (NOLOCK)
	ON TABELA.OBJECT_ID=COLUNA.OBJECT_ID
	JOIN SYS.TYPES AS TIPO WITH (NOLOCK)
	ON COLUNA.SYSTEM_TYPE_ID=TIPO.SYSTEM_TYPE_ID
	WHERE TABELA.TYPE_DESC='USER_TABLE'
	ORDER BY TABELA.NAME
	FOR XML AUTO) + '</TABELAS>' AS XML;