Create a Data Dictionary, from System views

[OlaSa]

I would like to use PowerQuary to get All TableCatalogues, TableNames and ColumnNames on an SQL Server, to create a "Data Dictionary".
Is it possible to do that?

I have tried to
1. First get a list of all TableCatalogues,
2. then create a Function for one TableCatalogue, and
3. then add a Column.
...but failed.


----
SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS

 

[ImkeF]

Have a look how this works:

let
    Quelle = Sql.Databases("YourServerName"),
    Budget1 = Quelle{[Name="YourDBName"]}[Data],
    MergeSchema = Table.NestedJoin(Budget1,{"Item"},Sql.Database("srvsql2", "Budget", [Query="SELECT * FROM INFORMATION_SCHEMA.TABLES"]),{"TABLE_NAME"},"NewColumn",JoinKind.LeftOuter),
    ShowCatalog = Table.ExpandTableColumn(MergeSchema, "NewColumn", {"TABLE_CATALOG"}, {"TABLE_CATALOG"}),
    ColNames = Table.AddColumn(ShowCatalog, "ColNames", each Table.ColumnNames([Data])),
    ShowColNames = Table.ExpandListColumn(ColNames, "ColNames")
in
    ShowColNames

 

[OlaSa]

This how we solved the DataDictionary question:
This Table contains 3 columns: DatabaseName, TableName, ColumnName
Enjoy. //Ola.S


--- HumanityTaxes paid - please improve

USE [QX_YYYOAS]
GO
/****** Object:  StoredProcedure [dbo].[p_CT_DataDictionary]    Script Date: 2016-04-01 12:57:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[p_CT_DataDictionary]
as

if exists(select 1 from sys.tables where name = 'DataDictionary')
drop table DataDictionary

CREATE TABLE [dbo].[DataDictionary](
[DatabaseName] [sysname] NOT NULL,
[TABLE NAME] [sysname] NULL,
[COLUMN NAME] [sysname] NULL
)

exec sp_MSforeachdb '
USE [?]; 
insert into QX_YYYOAS..DataDictionary
SELECT  
''?'' AS [DatabaseName], 
T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME]
FROM SYS.tables AS T
JOIN SYS.columns AS C
ON T.object_id  =C.object_id
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.schema_id=1
'