Verifying Column Definitions Across Tables And Views
Aug 22, 2015
Let me explain in simplified terms. We have an ETL process:
1) download data from the iSeries table Cust into SQL table Cust.
2) Tran_Cust is a view created FROM Cust
3) Copy from view Tran_Cust to table ods_Cust
4) v_ods_Cust is a view created from ods_Cust
I want to look for inconsistencies in the column definitions as a column moves from iSeries Cust, SQL Cust, view Tran_Cust, ods_Cust, view v_ods_Cust. The columns generally keep the same names except for v_ods_cust. Here a column like CustName will be created with a more meaningful column name - "CustName as 'Customer Name'.
We have hundreds of tables and we have column definition inconsistences where the host changed their column defintion and we made no changes on the SQL side. Or someone made a SQL column change in one table, but not both tables and the views were not dropped and recreated.
I have the list of tables. Think that I can put together some tsql to loop through the list of tables and create a new table based on the iSeries definition, then compare columns defintion across all the tables and views - except for the v_ods. THis is where columns are given new names - such as "CustName as 'Customer Name'". Need the actual view definition to find that CustName is 'Customer Name' and then compare the definition of ods_Cust.CustName to v_ods_Cust.[Customer Name]
How can I find the view definiton of say v_ods_Cust, seperate out the source column names (CustName) and destination column names (Customer Name) so that I can compare the source column names to another table and fine the differences?
Hi all, After installing SP2 for SQL2005, My boss has found new reports when looking for database information. Specifically, the new Reports option when right clicking on a database.
To better streamline our dbs he found a pair of Index reports that he does not know what to do with. Can anyone help us understand the time length for the numbers shown under #User Seeks, #User Scans, #User Updates, Last User Seek Time, Last User Scan Time, etc..? We are unsure if these are daily, or from the last backup, or for uptime since last restart, or collected from the inception of the database.
Any help would be good and very much appreciated as I am VERY new to this and would like to show that I can find information when asked.
I was wondering if there is an easy way to get a database schema out of a MS SQL 7 database indicating specs on each field (datatype,length,table it resides in, whether it allows nulls, default values, primary keys, foreign keys, etc.)
The boss has decided that the data from a table we have on one database (containing daily data) needs to be copied to a "history" table on a different database, on a different server.
The transfer will probably be done with a scheduled stored procedure, and all columns will be transferred EXCEPT for two columns in the source table, which are not present in the destination table. This means instead of an "INSERT Dest SELECT * FROM Source" I have to do an "INSERT Dest SELECT yada,yada,yada... FROM Source" in order to disregard the columns we don't care about. NO problem.
I was thinking (you should smell trouble - and sawdust burning at this point) that this leaves us open to a punch below the belt later when a new column is added to the Source table, since we are using a definitive list of columns to move rather than a *, and the new column could be added without the otherwise system-generated, *-sponsored "gentle reminder" that it also needs to be added to the Dest table. I'm not as convinced as The Boss that "Oh, I'm sure we'll remember to add it to the history table when that happens" :lol:
So, long story shorter (nah...too late for that) - I figure I can write a check at each day's historical transfer on the count of columns in one table verses the other, and send an email or fail the job if the count doesn't make sense.
For example, if the Source table has 34 columns, since we don't care about two of them, the Dest table should have 32 columns, if things are still in synch. OK, I think (insert burning sawdust smell here), I can use a SysObjects thang to count rows. BOL points out INFORMATION_SCHEMA.COLUMNS as a possibility.
Here is the code I think (sawdust) can be used:select ((select count(*) from SourceDb.INFORMATION_SCHEMA.columns where table_name = 'Source') - (select count(*) from HISTSERVER.DestDb.INFORMATION_SCHEMA.columns where table_name = 'Dest')) as ColumnDiff
Trouble is...this fails because apparently the INFORMATION_SCHEMA thang doesn't do well with remote servers.
Can anyone figure a way around this, or suggest an alternative? I'm still looking, but thought I'd also toss it out onto the board for your generous consideration.
Thanks - and can you BELIEVE the year is already 1/12th of the way OVER?!?!?! Paul
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view ( Name: Product Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc Table 2: Is a lookup table that provides the names of columns Name: lookupTable Structure: tableName, ColumnName, columnValue Values: Product, Attribute1, Color Product, Attribute2, Size Product, Attribute3, Flavor Product, Attribute4, Shape
Is there a way of uploading column definitions from the Flat File Connection Manager into a SQL Server table definition. Since I have over a dozen data sources to process each with around 200 columns and of course like many BI techies I have little immediate influence over the structure of these flat files. I just know that these data sources are business critical.
Judging by looking at similar threads I can't be the only one who would greatly benefit from being able to upload column definitions from the Flat File Connection Manager into a SQL Server table definition as opposed to doing this manually.
I am an SQL beginner / intermediate. My question is assuming everything is equal is a view of a 4 table join slower than the SQL query of the 4 table join when running reports?
My client has a three tier system. and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly
Are views used for securiry reasons, such as restricting access to certain columns/rows or is there performance advantages to using views. There is disagreement in my dept.
I created the following view PAYACTIVE:
Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'
The PAYACTIVE view contains 260 rows. The PAYMENTS table contains over 2 million rows. I executed the following commands:
Select * FROM PAYACTIVE
Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'
They seem to take the exact same amount of time. In other words, selecting from the view seemed to not help performance. Am I conducting a valid test?
My client has a three tier system. and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly>
I have a SQL script that I attempted to write into a view. If I run the script in SQL Query Analyzer it works perfectly. When I attempt to run execute as a view I receive the following error message: "[MS][ODBC SQL Server Driver][SQL Server] The query and the views or function in it exceeded the limit of 256 tables."
My objective is to use the recordset from the view to create a crystal report but have been unable to replicate the "query" effect directly in crystal. (unfortunately I am also a new crystal user)
All my searches have been fruitless. I remember seeing something about SQL Server 2000 supporting 1000 tables and older versions supporting 256 in an Access forum but have not been able to find that thread a second time.
Any provided solution would be greatly appreciated. (I am getting tired of hearing the "gears" in my head grinding. Is smoke from the ears normal?)
SELECT SubID, SubMenuDesc, ItemID, ItemDesc, 0 AS ModID, 'n/a' AS ModDesc, round(ItemPrice/100,2) as ItemPrice FROM vwSubMenuNorm WHERE (ItemPrice <> 0)
UNION SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID, vwModifiersNorm.ItemDesc AS ModDesc, round(vwModifiersNorm.ItemPrice/100,2) as ItemPrice FROM vwSubMenuNorm INNER JOIN vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID WHERE (vwSubMenuNorm.ItemPrice = 0) AND (vwModifiersNorm.ItemPrice <> 0)
UNION SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID, vwModifiersNorm.ItemDesc AS ModDesc, ROUND((vwSubMenuNorm.ItemPrice + vwModifiersNorm.ItemPrice)/100, 2) AS ItemPrice FROM vwSubMenuNorm INNER JOIN vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID WHERE (vwSubMenuNorm.ItemPrice <> 0) AND (vwModifiersNorm.ItemPrice <> 0)
I am using a Microsoft Access ADP to get to data on SQL Server 2000. I would like to protect the base tables from being edited directly, but allow the views and SPs to handle all the work of getting data in and out. From what I have read in numerous articles and boods, I should be able to grant permissions just on the Views and NOT on the tables. However, the ONLY way I can make my views 'updatable' is by graniting UPDATE permissions on the TABLE! Worse yet, if I DENY permissions to UPDATE, INSERT, and DELETE in the view, but allow them in the table, the view allows the updates anyway, apparently not looking at the fact that it should be DENIED. :confused:
We are attempting to implement security on top of a shrink-wrapped softwarepackage and are trying to get row-level security. Here's the scenario:1. Table dbo.BOOK contains all the information about books in everydepartment.2. There are a large number of developed reports that run queries like"select * from BOOK..."3. We wish to have each Department only be able to see their books - withoutchanging the existing reports.Our thought was to create a series of views:create view Dept1.BOOK asselect * from BOOK where Dept=1....and then create Roles for each Dept. We'd then remove rights to dbo.BOOKand grant rights to DeptN.BOOK as appropriate for each role. We startedtesting this and seemed to get it working, but are now having problems. Isthis possible? Is there another, better solution?Thanks!
I have an applicaton in which I collect data for different parametersfor a set of devices. The data are entered into a single table, eachset of name, value pairs time-stamped and associated with a device.The definition of the table is as follows:CREATE TABLE devicedata(device_idintNOT NULL REFERENCES devices(id),-- id in the devicetabledatetimedatetimePRIMARY KEY CLUSTERED,-- date creatednamenvarchar(256)NOT NULL,-- name of the attributevaluesql_variantNOT NULL-- value)For example, I have 3 devices, and each is monitored for two attributes-- temperature and pressure. Data for these are gathered at say every20 minute and every 15 minute intervals.The table is filled with records over a period of time, and I canperform a variety of SQL queries.I have another requirement which requires me to retrieve the *latest*values of temperature and pressure for each device.Ideally, I'd like to use the data I have collected to get thisinformation, and I suppose I can.What I need is the SELECT statement to do this.I'd appreciate it very much, if someone can help provide that.Conceivably, I could use a SQL server View for making this easier forsome of my users.One alternate technique I thought was to create another table which I*update* with the latest value, each time I *insert* into the abovetable. But it seems like a waste to do so, and introduces needlessreferential integrity issues (minor). Maybe for fast access, that isthe best thing to do.I have requirements to maintain this data for several months/year ortwo, so I am dealing with a large number of samples.Any help would be appreciated.(I apologize if this post appears twice)
I've got a table T1 and a view V1 based on T1's rows (but not all rows).
I've granted users to do update and select on my View V1. But i don't want users directly update my table T1.
then, i log as a standard user in Enterprise mgr, and open my view V1. I modify a value in a field, and when i validate, an error occurs saying i'm not allowed to make Select neither Update on table T1.
I am looking for an efficient mechanism to compare data between 2 tables/views.
Rationale: I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.
and BSmith's idea (no relation), I came up with this silly stored proc to compare any two tables or views. Supply the table names and the columns you wish to compare and have fun!
The key to the GROUP BY approach is that it handles NULLS quite well which JOINS have trouble with.
-- Table1, Table2 are the tables or views to compare. -- T1ColumnList is the list of columns to compare, from table1. -- Just list them comma-separated, like in a GROUP BY clause. -- If T2ColumnList is not specified, it is assumed to be the same -- as T1ColumnList. Otherwise, list the columns of Table2 in -- the same order as the columns in table1 that you wish to compare. -- -- The result is all records from either table that do NOT match -- the other table, along with which table the record is from.
declare @SQL varchar(8000);
IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList
set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList + ' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' + @t2ColumnList + ' FROM ' + @Table2
set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList + ' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList + ' HAVING COUNT(*) = 1'
Hello,We are researching whether the following scenario would be possible:In an upcoming application release, we have to move some tables (Logtables, look up tables, and a couple of secure tables) from database Ato database B.Rather than wait and do everything all at once, and have no roll-backplan should it fail, we'd like to create database B now, and startmoving those tables one by one over to it.To ensure compatibility with the existing code-base, we'd like todetermine whether we can use updatable views to allow the current codeto continue to run against the existing DB.Essentially, we would do this:Given a table named LogTable In database A, we'd copy all ofLogTable's data to database B. (We'd look at the transaction log tocopy any changes made on rows modified after copying started.)Then, we'd turn off the site for a few moments, and:In database A, we'd rename LogTable to LogTable-Old, and create a viewcalled LogTable which points to DatabaseB.dbo.LogTable.When we turn the site back on, updates and selects to LogTable wouldphysically pull from database B from now on.I have already verified that performing selects and updates against aview that refers to another physical database actually does work inSQL 2K5.My question is are there any pitfalls or things we should be aware ofthat anyone else has experienced trying to do something like this?Does it sound feasible?Thank you,Josh
Hi,I am not getting option as 'new view' and 'new table' when I rightclick onviews and tables option in VisualStudio.net IDE server explorersqlservers database to create new objects.Looks like some setup issue in my database.Thanks for your help in advance.RgdsCV
With my understanding of indexed views and according to books I read"indexed views" are supposed to perform much better than "temp tables"(temp table having primary key and indexed view with clustered indexon the same keys).But when I tried in my system I am getting opposite results. WithIndexed Views it takes 3 times more time.Any body has any reasons for that? Or my understanding was wrong?thanksRaghu Avirneni
I have a pretty large database that has tables that will contain millions of rows of records. I will predominantly be using Views just to select the data. (I will not be performing any updates or inserts). I propose creating indexes on the views. My question is - if I create indexes on my views, do I have to create them on the tables as well? Is it good practice to create indexes on tables by default even if I am not going to be performing select statements directly on my tables but via my indexed views? Any advice is appreciated.
Hi, I'm working with few examples from a 70-315 exam prep book. I'm trying to view the Table design... edit/ create views from VS. NET environment. But when i right click on the View node under Data Connection tree i get only "Refresh" & "Properties". According to this book.. i should be able to create new views from here. I'm working with a SQL Server 2000 local installation. VS .NET is connected to the DB, as i tried other examples and i'm able to read from the DB. My SQL server installation is a 120 days eval installation... has this got anything to do with the behavior ?? I also reied to establish a connection with SQL server 'sa' login... i could connect, but again i wasnt able to create 'views' access tables design. Any help would be appreciated.
Hello, I need to create a view which will include an additional table every week of our financial year. Example: Every week we create a table called Sales_YYYY_WW where YYYY is the Year and WW is the Week, so after the first week we have the table Sales_2007_01 and after the second week we have Sales_2007_01 and Sales_2007_02 etc.. These tables are created evey week and don't exist at the start of the financial year. I'd like to create a view at the beginning of each year to encompass each table evey week as it is created. If all of the tables were there at the start of the year it would be simple: CREATE VIEW VIEW_SALES_2007 AS SELECT * from Sales_2007_01 UNION ALL SELECT * from Sales_2007_02 etc. But I don't know how to achieve this when the tables don't already exist. I don't want to have to edit the view each week to add in the new table.
As a CRM person, I sometimes have to update the database to cope with custom reports that I have written. Usually, I will
Go into SQL Server Management Studio 2008Open the database Locate either the table or the view Right click on permissions. Add in the role or user that's needed Grant them Select permissions
This allows the report in CRM to access the database and this works.
The problem I have is that after a certain amount of time, the user or role will disappear from the permissions meaning that the report will not run. Sometimes, this can be as soon as a couple of days or it can be months. It happens on views more than tables and, whilst it's not too difficult to put the permissions back, it is annoying for the users.
I don't use SQL Server Management Studio very regularly, only to write a few queries and set up these permissions so I'm not sure if there's anything else in there that might be doing this.
