Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to incorporate an existing SQL script into QV and not having much success. SQL isn't my strongest area...
We need a Network/DB schema that includes Hash ID's. I'm not sure how to implement it.
Here's what I have so far:
Connection string;
//Build base table
Execute('SELECT s.name AS SchemaName
, t.name AS TableName
, c.name AS ColumnName
, 0 AS IsHashID
INTO #SchemaCrawler
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id');
QV says there is a script line error. Am I going about this the right way?
Thanks!
JC
Hi,
Try:
Connection string;
SchemaCrawler:
Load *;
SQL SELECT s.name AS SchemaName
, t.name AS TableName
, c.name AS ColumnName
, 0 AS IsHashID
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id;
Thank you both for your assistance. I now have all of the code that I need to incorporate into QV.
I have to loop it through nine (9) servers. Any thoughts as to how it can be implemented?
//Build base table
SELECT s.name AS SchemaName
, t.name AS TableName
, c.name AS ColumnName
, 0 AS IsHashID
INTO #SchemaCrawler
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id;
//Run a cursor
DECLARE @database
DECLARE @schema VARCHAR(15)
DECLARE @table VARCHAR(255)
DECLARE @column VARCHAR(255)
DECLARE @sql VARCHAR(MAX)
DECLARE LoopTables CURSOR FAST_FORWARD READ_ONLY FOR
SELECT s.name, t.name, c.name
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.name = 'hashid'
AND t.name = 'plsubdatahash'
OPEN LoopTables
FETCH NEXT FROM LoopTables INTO @schema, @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'INSERT INTO #SchemaCrawler (SchemaName, TableName, ColumnName, IsHashID) SELECT DISTINCT ''' + @schema + ''' as SchemaName, ''' + @table + ''' as TableName, hashid AS ColumnName, 1 as IsHashID FROM ' + @schema + '.' + @table
EXEC sp_executesql @sql
FETCH NEXT FROM LoopTables INTO @schema, @table, @column
END
CLOSE LoopTables
DEALLOCATE LoopTables
SELECT *
FROM #SchemaCrawler
Please disregard this post.
After getting clarification on the requirements, I need to start a new post.