August 21, 2020 at 6:10 am
We keep running into issues where life comes to a grinding halt due to tables with INT IDENITY columns hit the max int value. Then we have to scramble to try and get a table with 2bil records modified to use BIGINT.
I am working on a little project to change all INT columns to BIGINT. However, with more than 400 servers and 1000s of tables, I can't do it all at once. I want to batch a script out to all of the DBs, that will return a list of table names along with the columns that have INT datatypes, and the max value in each of those columns. This will allow me to prioritise the DBs and tables.
Seems is easy enough. However, many of the tables have PII data, so all access is removed, and the data is exposed via views that return '*****' for any PII data - Not an issue, as I do not need to read any PII columns. My problem is in mapping the PII view coulmns back to the underlying table column.
The code below is a mockup of the scenario. I have identified 2 methods to determine a list of the views and the tables that they are covering, along with the table column names.
What I need is to figure out the mapping between ViewName.ColumnName and TableName.ColumnName. My scripts need to be SQL 2014 compliant (Yes I know, but I can't change the environment. The DBAs have started a project to upgrade to SQL 2019.)
Create a test table and view to illustrate the issue
USE tempdb
GO
IF OBJECT_ID(N'dbo.TestView', N'V') IS NOT NULL
BEGIN
DROP VIEW dbo.TestView;
END;
IF OBJECT_ID(N'dbo.TestTable', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.TestTable;
END;
GO
CREATE TABLE dbo.TestTable (
ID int NOT NULL
, F1 int NULL
, F2 varchar(10) NULL
);
GO
CREATE VIEW dbo.TestView
AS
SELECT
ID
, strVal = F2
, intVal = F1
FROM dbo.TestTable;
GO
The 2 SQL scripts that I have to get most of the required info. I need to be able to find any of the '???' fields below.
SELECT
ViewID = v.object_id
, ViewSchemaName = SCHEMA_NAME(v.schema_id)
, ViewName = v.name
, ViewColumnName = '???' -- Need to find the view alias/columnName that maps to the table column
, ViewColumnOrdinal = '???' -- or the view column ordinal position
, TableID = sd.referenced_major_id
, TableSchemaName = OBJECT_SCHEMA_NAME(sd.referenced_major_id)
, TableName = OBJECT_NAME(sd.referenced_major_id)
, TableColumnName = tc.name
, TableColumnOrdinal = sd.referenced_minor_id
FROM sys.views AS v
INNER JOIN sys.sql_dependencies AS sd ON sd.object_id = v.object_id
INNER JOIN sys.columns AS tc ON tc.object_id = sd.referenced_major_id AND tc.column_id = sd.referenced_minor_id
-- INNER JOIN sys.columns AS vc ON vc.object_id = v.object_id AND vc.column_id = ??? -- Used to find the View ColumnName/Alias
WHERE v.name = 'TestView'
SELECT
ViewID = v.object_id
, ViewSchemaName = SCHEMA_NAME(v.schema_id)
, ViewName = v.name
, ViewColumnName = '???' -- Need to find the view alias/columnName that maps to the table column
, ViewColumnOrdinal = '???' -- or the view column ordinal position
, TableID = ref.referenced_id
, TableSchemaName = ref.referenced_schema_name
, TableName = ref.referenced_entity_name
, TableColumnName = ref.referenced_minor_name
, TableColumnOrdinal = ref.referenced_minor_id
FROM sys.views AS v
CROSS APPLY sys.dm_sql_referenced_entities(SCHEMA_NAME(v.schema_id) + '.' + v.name, N'OBJECT') AS ref
-- INNER JOIN sys.columns AS vc ON vc.object_id = v.object_id AND vc.column_id = ??? -- Used to find the View ColumnName/Alias
WHERE v.name = 'TestView'
AND ref.referenced_minor_id > 0
August 21, 2020 at 2:59 pm
What about INFORMATION_SCHEMA.COLUMNS or sys.columns ? Do you not have access to either of those? It just seems to me that those would make it far easier than trying to map a view to an underlying table. This might need to be a DBA only type of action taken, at least in your environment, but I can't really think of a good reason to want to map the view to the underlying table when those system tables and views exist.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 21, 2020 at 4:50 pm
I don't see any way to do this. A view could have columns from several different referenced entities and from no referenced entity at all (such as CREATE VIEW dbo.view_name AS SELECT GETDATE() AS run_date, ...).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 24, 2020 at 5:34 am
What about INFORMATION_SCHEMA.COLUMNS or sys.columns ? Do you not have access to either of those? It just seems to me that those would make it far easier than trying to map a view to an underlying table. This might need to be a DBA only type of action taken, at least in your environment, but I can't really think of a good reason to want to map the view to the underlying table when those system tables and views exist.
Thanks for the reply Steve. The INFORMATION_SCHEMA views are built directly on the sys.views.
I have looked at both, and they give me a list of columns in the views, as well as the underlying tables that they map to. However, there is nothing to indicate the mapping between the view column and the table column.
August 24, 2020 at 5:36 am
I don't see any way to do this. A view could have columns from several different referenced entities and from no referenced entity at all (such as CREATE VIEW dbo.view_name AS SELECT GETDATE() AS run_date, ...).
Thanks for the reply Scott. Agreed on the GETDATE() type of columns. I just find it strange that the list of tables and columns referenced is tracked, but not the aliases.
August 24, 2020 at 5:40 am
Luckily, in this case, the PII views that sit on top of the underlying tables, all return data from only that table, and use exactly the same column names.
SO, I was able to create a script by using the column names from the views, and simply swapping out the table names with view names. This works for now, but my gut is screaming that it will come back to bight me if (or rather WHEN)somebody decides to get fancy with the views in the future.
August 24, 2020 at 1:28 pm
Nice. Don't feel bad, we have views that do this:
SELECT ...
FROM OPENQUERY(remoteserver1, '...')
UNION ALL
SELECT...
FROM OPENQUERY(removeserver2, '...')
UNION ALL
SELECT ...
FROM dbo.local_table
No way to match up all the sources to the destination for that conglomeration!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 25, 2020 at 1:41 pm
Yeah, Des, there's really no GOOD way to map the view's columns. After all, the purpose of a view is often to obfuscate the existence of the table for security reasons, and to help avoid direct table access. It wouldn't be all that useful for such a purpose if there was an easy way to defeat that...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply