Usuário com melhor resposta
Como fazer um select com um levantamento geral do BANCO-TABELA-ATRIBUTOS

Pergunta
-
Boa tarde, estou com dificuldades em montar um select que me tragam algumas informações em apenas um select. Em perguntas anteriores que ja fiz sobre o mesmo assunto, o pessoal respondia e depois desaparecia, apesar da resposta ter contribuído porem não finalizou o meu problema.
preciso juntar as seguintes informacoes:
ID DA BASE;
NOME DA DATABASE;
DESCRICAO DA BASE;
ID DA TABELA;
NOME DA TABELA;
DESCRICAO DA TABELA;
INDEX DA CAMPO;
NOME DA CAMPO;
DESCRICAO DO CAMPO;
TIPO DE DADO(INT, VARCHAR...);
SE ACEITA NULL;
SE É PK;
SE É UNIQUE;
SE POSSUI CONSTRAINT;
SE É FK;
SE FOR FK PARA QUAL TABELA REFERENCIA;
SE FOR FK PARA QUAL CAMPO REFERENCIA;
Agradeço se alguém puder me ajudar a resolver meu problema...
Segue ate onde cheguei ate o momento:
SELECT USER_NAME(obj.uid) AS UserName, (SELECT name FROM sys.extended_properties WHERE class = 0) AS 'DATABASE', (SELECT value FROM sys.extended_properties WHERE class = 0) AS 'DATABASE', obj.id AS TableID, obj.name AS TableName, (SELECT TOP 1 value FROM sys.extended_properties WHERE major_id = obj.id AND minor_id = 0) AS 'Descricao Tabela', col.name AS FieldName, col.colorder AS ColumnOrder, TYPE_NAME(col.xusertype) + CASE WHEN TYPE_NAME(col.xusertype) = 'varchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' WHEN TYPE_NAME(col.xusertype) = 'nvarchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' WHEN TYPE_NAME(col.xusertype) = 'char' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' WHEN TYPE_NAME(col.xusertype) = 'nchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' ELSE CONVERT(varchar, '') END AS DataType, CONVERT(varchar, pro.value) AS 'Descricao Atributo', CASE col.IsNullable WHEN 1 THEN 'Yes' ELSE 'No' END AS IsNullable FROM sysobjects obj INNER JOIN syscolumns col ON obj.id = col.id LEFT JOIN sysobjects def ON obj.id = def.parent_obj AND def.info = col.colorder AND def.xtype IN ('D ') LEFT JOIN syscomments com ON def.id = com.id LEFT JOIN sys.extended_properties pro ON obj.id = pro.major_id AND col.colorder = pro.minor_id LEFT JOIN sys.extended_properties prot ON prot.major_id = obj.id AND prot.minor_id = 0 WHERE obj.xtype = 'U' AND obj.name != 'dtproperties' AND obj.id <> 1563152614 ORDER BY OBJECT_NAME(obj.id), col.colorder
Respostas
-
Acabei conseguindo, tenho certeza que deve haver algum modo mais fácil que esse mas por hora é esse mesmo.
Segue o T-SQL, para os demais que precisarem:
WITH DICIONARIO_DADOS AS (SELECT USER_NAME(obj.uid) AS UserName, (SELECT name FROM sys.extended_properties WHERE class = 0) AS 'BASE', (SELECT value FROM sys.extended_properties WHERE class = 0) AS 'BASEDESCRICAO', obj.id AS TableID, obj.name AS TableName, (SELECT TOP 1 value FROM sys.extended_properties WHERE major_id = obj.id AND minor_id = 0) AS 'TabelaDescricao', col.name AS FieldName, col.colorder AS ColumnOrder, TYPE_NAME(col.xusertype) + CASE WHEN TYPE_NAME(col.xusertype) = 'varchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' WHEN TYPE_NAME(col.xusertype) = 'nvarchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' WHEN TYPE_NAME(col.xusertype) = 'char' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' WHEN TYPE_NAME(col.xusertype) = 'nchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' ELSE CONVERT(varchar, '') END AS DataType, CONVERT(varchar, pro.value) AS 'ColunaDescricao', CASE col.IsNullable WHEN 1 THEN 'Yes' ELSE 'No' END AS IsNullable FROM sysobjects obj INNER JOIN syscolumns col ON obj.id = col.id LEFT JOIN sysobjects def ON obj.id = def.parent_obj AND def.info = col.colorder AND def.xtype IN ('D ') LEFT JOIN syscomments com ON def.id = com.id LEFT JOIN sys.extended_properties pro ON obj.id = pro.major_id AND col.colorder = pro.minor_id LEFT JOIN sys.extended_properties prot ON prot.major_id = obj.id AND prot.minor_id = 0 WHERE obj.xtype = 'U' AND obj.name != 'dtproperties' AND obj.id <> 1563152614), REFERENCIAS AS (SELECT col.TABLE_CATALOG, col.TABLE_NAME, col.ORDINAL_POSITION, col.COLUMN_NAME, col.IS_NULLABLE, (CASE (SELECT SUBSTRING(CONSTRAINT_NAME, 1, 2) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE col.TABLE_NAME = TABLE_NAME AND col.COLUMN_NAME = COLUMN_NAME AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'PK') WHEN 'PK' THEN 'true' ELSE null END ) AS 'Primarykey', (CASE (SELECT SUBSTRING(CONSTRAINT_NAME, 1, 2) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE col.TABLE_NAME = TABLE_NAME AND col.COLUMN_NAME = COLUMN_NAME AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'UK') WHEN 'UK' THEN 'true' ELSE null END ) AS 'UniqueKey', (CASE (SELECT SUBSTRING(CONSTRAINT_NAME, 1, 2) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE col.TABLE_NAME = TABLE_NAME AND col.COLUMN_NAME = COLUMN_NAME AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'CT') WHEN 'CT' THEN 'true' ELSE null END ) AS 'Constrain', (CASE (SELECT SUBSTRING(CONSTRAINT_NAME, 1, 2) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE col.TABLE_NAME = TABLE_NAME AND col.COLUMN_NAME = COLUMN_NAME AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'FK') WHEN 'FK' THEN 'true' ELSE null END ) AS 'ForeingKey' FROM INFORMATION_SCHEMA.COLUMNS AS col (NOLOCK)) SELECT TABLE_CATALOG, basedescricao, TableID, TABLE_NAME, TabelaDescricao, ColumnOrder, COLUMN_NAME, ColunaDescricao, IsNullable, Primarykey, UniqueKey, Constrain, ForeingKey, (CASE Foreingkey WHEN 'true' THEN (SELECT objj.name AS TabelaPK FROM sys.foreign_key_columns AS fkc INNER JOIN sys.objects AS obj ON fkc.parent_object_id = obj.object_id INNER JOIN sys.objects AS objj ON fkc.referenced_object_id = objj.object_id INNER JOIN sys.objects AS objcts ON fkc.constraint_object_id = objcts.object_id WHERE TABLE_NAME = obj.name AND COLUMN_NAME = (SELECT name FROM syscolumns WHERE colid = fkc.parent_column_id AND id = fkc.parent_object_id)) END) AS TabelaReferenciaFK, (CASE Foreingkey WHEN 'true' THEN (SELECT (SELECT name FROM syscolumns WHERE colid = fkc.referenced_column_id AND id = fkc.referenced_object_id) AS ColunaPKdaFK FROM sys.foreign_key_columns AS fkc INNER JOIN sys.objects AS obj ON fkc.parent_object_id = obj.object_id INNER JOIN sys.objects AS objj ON fkc.referenced_object_id = objj.object_id INNER JOIN sys.objects AS objcts ON fkc.constraint_object_id = objcts.object_id WHERE TABLE_NAME = obj.name AND COLUMN_NAME = (SELECT name FROM syscolumns WHERE colid = fkc.parent_column_id AND id = fkc.parent_object_id)) END) AS TabelaReferenciaFKa FROM DICIONARIO_DADOS INNER JOIN REFERENCIAS ON BASE = TABLE_CATALOG AND TableName = TABLE_NAME AND FieldName = COLUMN_NAME --where constrain <> NULL order by table_name
- Marcado como Resposta Durval RamosModerator terça-feira, 11 de novembro de 2014 01:23
Todas as Respostas
-
Fabiano,
Existem varias views no SQL Server no schema [INFORMATION_SCHEMA]
Da uma olhada nelas, você irá encontrar o que esta querendo.
Exemplo: select * from [INFORMATION_SCHEMA].[COLUMNS]
Tulio Rosa | http://tuliorosa.com.br | Se resolveu seu problema, marque como resposta ou vote
-
-
Em paralelo ao select postado acima, fiz outro para tentar ir por outro caminho, mas nesse não consigo pegar o id da BASE, da tabela , nem a descricao (propriedade extentida) delas e no caso das que forem FK, para quais tabela e campo fazem referencia. Alguém ajuda porfavor!
select col.TABLE_CATALOG, col.TABLE_NAME, col.ORDINAL_POSITION, col.COLUMN_NAME, col.IS_NULLABLE , ( case (select SUBSTRING(CONSTRAINT_NAME,1,2) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where col.TABLE_NAME = TABLE_NAME and col.COLUMN_NAME = COLUMN_NAME and SUBSTRING(CONSTRAINT_NAME,1,2) = 'PK') when 'PK' then 'true' else '' end ) as 'Primarykey' , ( case (select SUBSTRING(CONSTRAINT_NAME,1,2) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where col.TABLE_NAME = TABLE_NAME and col.COLUMN_NAME = COLUMN_NAME and SUBSTRING(CONSTRAINT_NAME,1,2) = 'UK') when 'UK' then 'true' else '' end ) as 'Unique key' , ( case (select SUBSTRING(CONSTRAINT_NAME,1,2) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where col.TABLE_NAME = TABLE_NAME and col.COLUMN_NAME = COLUMN_NAME and SUBSTRING(CONSTRAINT_NAME,1,2) = 'CT') when 'CT' then 'true' else '' end ) as 'Constraint' , ( case (select SUBSTRING(CONSTRAINT_NAME,1,2) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where col.TABLE_NAME = TABLE_NAME and col.COLUMN_NAME = COLUMN_NAME and SUBSTRING(CONSTRAINT_NAME,1,2) = 'FK') when 'FK' then 'true' else '' end ) as 'Foreing Key' from INFORMATION_SCHEMA.COLUMNS as col (nolock) order by col.TABLE_NAME
-
Acabei conseguindo, tenho certeza que deve haver algum modo mais fácil que esse mas por hora é esse mesmo.
Segue o T-SQL, para os demais que precisarem:
WITH DICIONARIO_DADOS AS (SELECT USER_NAME(obj.uid) AS UserName, (SELECT name FROM sys.extended_properties WHERE class = 0) AS 'BASE', (SELECT value FROM sys.extended_properties WHERE class = 0) AS 'BASEDESCRICAO', obj.id AS TableID, obj.name AS TableName, (SELECT TOP 1 value FROM sys.extended_properties WHERE major_id = obj.id AND minor_id = 0) AS 'TabelaDescricao', col.name AS FieldName, col.colorder AS ColumnOrder, TYPE_NAME(col.xusertype) + CASE WHEN TYPE_NAME(col.xusertype) = 'varchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' WHEN TYPE_NAME(col.xusertype) = 'nvarchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' WHEN TYPE_NAME(col.xusertype) = 'char' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' WHEN TYPE_NAME(col.xusertype) = 'nchar' THEN '(' + CONVERT(varchar, CONVERT(int, prec)) + ')' ELSE CONVERT(varchar, '') END AS DataType, CONVERT(varchar, pro.value) AS 'ColunaDescricao', CASE col.IsNullable WHEN 1 THEN 'Yes' ELSE 'No' END AS IsNullable FROM sysobjects obj INNER JOIN syscolumns col ON obj.id = col.id LEFT JOIN sysobjects def ON obj.id = def.parent_obj AND def.info = col.colorder AND def.xtype IN ('D ') LEFT JOIN syscomments com ON def.id = com.id LEFT JOIN sys.extended_properties pro ON obj.id = pro.major_id AND col.colorder = pro.minor_id LEFT JOIN sys.extended_properties prot ON prot.major_id = obj.id AND prot.minor_id = 0 WHERE obj.xtype = 'U' AND obj.name != 'dtproperties' AND obj.id <> 1563152614), REFERENCIAS AS (SELECT col.TABLE_CATALOG, col.TABLE_NAME, col.ORDINAL_POSITION, col.COLUMN_NAME, col.IS_NULLABLE, (CASE (SELECT SUBSTRING(CONSTRAINT_NAME, 1, 2) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE col.TABLE_NAME = TABLE_NAME AND col.COLUMN_NAME = COLUMN_NAME AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'PK') WHEN 'PK' THEN 'true' ELSE null END ) AS 'Primarykey', (CASE (SELECT SUBSTRING(CONSTRAINT_NAME, 1, 2) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE col.TABLE_NAME = TABLE_NAME AND col.COLUMN_NAME = COLUMN_NAME AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'UK') WHEN 'UK' THEN 'true' ELSE null END ) AS 'UniqueKey', (CASE (SELECT SUBSTRING(CONSTRAINT_NAME, 1, 2) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE col.TABLE_NAME = TABLE_NAME AND col.COLUMN_NAME = COLUMN_NAME AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'CT') WHEN 'CT' THEN 'true' ELSE null END ) AS 'Constrain', (CASE (SELECT SUBSTRING(CONSTRAINT_NAME, 1, 2) FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE col.TABLE_NAME = TABLE_NAME AND col.COLUMN_NAME = COLUMN_NAME AND SUBSTRING(CONSTRAINT_NAME, 1, 2) = 'FK') WHEN 'FK' THEN 'true' ELSE null END ) AS 'ForeingKey' FROM INFORMATION_SCHEMA.COLUMNS AS col (NOLOCK)) SELECT TABLE_CATALOG, basedescricao, TableID, TABLE_NAME, TabelaDescricao, ColumnOrder, COLUMN_NAME, ColunaDescricao, IsNullable, Primarykey, UniqueKey, Constrain, ForeingKey, (CASE Foreingkey WHEN 'true' THEN (SELECT objj.name AS TabelaPK FROM sys.foreign_key_columns AS fkc INNER JOIN sys.objects AS obj ON fkc.parent_object_id = obj.object_id INNER JOIN sys.objects AS objj ON fkc.referenced_object_id = objj.object_id INNER JOIN sys.objects AS objcts ON fkc.constraint_object_id = objcts.object_id WHERE TABLE_NAME = obj.name AND COLUMN_NAME = (SELECT name FROM syscolumns WHERE colid = fkc.parent_column_id AND id = fkc.parent_object_id)) END) AS TabelaReferenciaFK, (CASE Foreingkey WHEN 'true' THEN (SELECT (SELECT name FROM syscolumns WHERE colid = fkc.referenced_column_id AND id = fkc.referenced_object_id) AS ColunaPKdaFK FROM sys.foreign_key_columns AS fkc INNER JOIN sys.objects AS obj ON fkc.parent_object_id = obj.object_id INNER JOIN sys.objects AS objj ON fkc.referenced_object_id = objj.object_id INNER JOIN sys.objects AS objcts ON fkc.constraint_object_id = objcts.object_id WHERE TABLE_NAME = obj.name AND COLUMN_NAME = (SELECT name FROM syscolumns WHERE colid = fkc.parent_column_id AND id = fkc.parent_object_id)) END) AS TabelaReferenciaFKa FROM DICIONARIO_DADOS INNER JOIN REFERENCIAS ON BASE = TABLE_CATALOG AND TableName = TABLE_NAME AND FieldName = COLUMN_NAME --where constrain <> NULL order by table_name
- Marcado como Resposta Durval RamosModerator terça-feira, 11 de novembro de 2014 01:23