Quickly read company specific table data for each company with SQL

RemkoDRemkoD Posts: 55Member
edited 2018-03-09 in SQL Tips & Tricks
Sometimes I need to check table data from a company specific table in each company. A quick lookup from MSSQL can make live easier. I've made an easy query for this.

In this example I want a quick overview from all the job queues and entries in the NAV database.
DECLARE @TableName varchar (50) = 'Job Queue'
DECLARE @TableName2 varchar (50) = 'Job Queue Entry'

SELECT 
	'SELECT ''' + [Name] + ''' AS "NAV Company ('+ [Name] +')", * FROM [dbo].[' + REPLACE([Name], '.', '_') + '$' + @TableName + ']' AS "Generated Query 1", 
	'SELECT ''' + [Name] + ''' AS "NAV Company ('+ [Name] +')", * FROM [dbo].[' + REPLACE([Name], '.', '_') + '$' + @TableName2 + ']' AS "Generated Query 2"
FROM [dbo].[Company]

This query generates new queries.

vd3fddy82ut3.png

You can execute all generated queries to get the table data from all the companies. If you change the table name in the declare statement you can use this query for other tables as well.

Best Answer

Answers

  • RemkoDRemkoD Posts: 55Member
    It wasn't a question. I thought it was a handy query to share. It would've saved me a few minutes if I had found an example. Isn't that where the SQL Tips & Tricks topic is for?
Sign In or Register to comment.