jose11au

<div>
Hi Ari,<br>Thank you for your help.<br>Do you know the steps to add the syntax to excel and run it?<br><br>thank you.<br><br><br>
</div>


<div>
&gt;Do you know the steps to add the syntax to excel and run it?&nbsp;<br>Without digging into the weeds of this question, read&nbsp;<a href="https://www.experts-exchange.com/articles/13675/Microsoft-Excel-SQL-Server-Self-service-BI-to-give-users-the-data-they-want.html" rel="ugc">Microsoft Excel &amp; SQL Server: &nbsp;Self service BI to give users the data they want</a> for a demo on how to serve up a set of data via Stored Procedure and cosmetically display it in Excel.&nbsp;
</div>


<div>
1. Choose some DB as SytemDB.<br>2. Create linked servers to other SQL Server Instances.<br>3. Collect information in SystemDB<br>4. You can use export from SystemDB to Excel.<br><br>Linked Servers allow you use 4 parts queries like:<br>SELECT 'SqlServer1' AS SqlServer, s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName<br>FROM SqlServer1.master.sys.tables t<br>JOIN SqlServer1.master.sys.schemas s ON t.schema_Id=s.schema_Id<br>JOIN SqlServer1.master.sys.columns n ON n.object_id = t.object_id<br><br>it could be organized in cycles<br>DECLARE @serverName sysname, @dbName sysname, @sql NVarChar(max);<br>DECLARE @servers (name sysname)<br>SELECT name INTO @servers FROM SystemDB ORDER BY name<br>&nbsp;DECLARE cr CURSOR FOR SELECT name FROM @servers<br>WHILE 1=1 BEGIN<br>&nbsp; &nbsp; FETCH NEXT FROM cr INTO @serverName<br>&nbsp; &nbsp; IF @@FETCH_STATUS &lt;&gt;0 BREAK<br>&nbsp; &nbsp; SET @sql = 'DECLARE crDB CURSOR FOR '+<br>&nbsp; &nbsp; &nbsp;'SELECT name FROM ' + @serverName + '.master.sys.databases d '+<br>&nbsp; &nbsp; &nbsp;' ORDER BY d.name '<br>&nbsp; &nbsp; EXEC (@sql)<br>&nbsp; &nbsp; OPEN crDB;<br>&nbsp; &nbsp; WHILE 1=1 BEGIN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;....<br>&nbsp; &nbsp; &nbsp;END<br>&nbsp; &nbsp; &nbsp;CLOSE crDB;<br>&nbsp; &nbsp; &nbsp;DEALLOCATE crDB<br>END<br>CLOSE cr;<br>DEALLOCATE cr;<br><br>You can schedule MS SQL Job to run stored procedure that will gather data and update SystemDB.<br><br>Then, when you need it in Excel you will export it from SystemDB and it will work quite quickly.
</div>


<div>
Thank you so much for all you help.<br>Appreciated.<br><br>
</div>


<div>
<div class="content wysiwyg-content fr-view">
Hi All,<br><br>I need your assistance with exporting SQL Server tables and columns to Excel in an automated way. <strong>No data</strong>.<strong>&nbsp;Just table and column names.</strong><br><br>I have attached a sample of the format I would like to display table and column names. Basically, select the server, database, table and columns are show.<br><br>Any assistance is highly appreciated.<br><br>Thank you in advance.<br><a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559416/EE.PNG" rel="ugc"><img src="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559416/EE.PNG" class="fr-fic fr-dib"></a>
</div>
</div>


EE.PNG


Hi All,
I need your assistance with exporting SQL Server tables and columns to Excel in an automated way.
No data
.
 Just table and column names.
I have attached a sample of the format I would like to display table and column names. Basically, select the server, database, table and columns are show.
Any assistance is highly appreciated.
Thank you in advance.

SQL Server tables and columns to Excel in an automated way. No data. Table and columns only.

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.