?????? In SQL Server 2000, you cannot find the most base table field dependence of multi-level views by Sysdepends, SysColumns, etc., especially when the view is aliased. However, in some environments, the software system wants to achieve this, so there is only the idea of analyzing during logic, this is undoubtedly low, but it is an implementation method. The idea of this code provided is that the analysis view of Create View .. as select ... from ... This statement, intercept SELECT and FROM to proceed, then find such as dbo.yourtableorview.yourcolumn (AS The following sections, such as XXX, then further analysis, and YourColumn. This, then determine that this YourTableorView is a view or a table. If it is a table, the description has been found, if it is a view, then call this process to recurrent analysis It has been analyzed to the table. Of course, it is also considered to consider the complexity of the view, such as a field is a constant, or a computational column, etc. There is no case where the base table field corresponds, pay attention to the application, pay attention to it. ?
? ///
?? // Analyze the SQL statement of the view, get the base table and the base table field corresponding to a field ?? ///
?? ///
View of the Create View statement
?? ///
View field to be analyzed
?? ///
The source table (or view) of the view field to be analyzed
?? ///
The view field to be analyzed in the field name in the source table or view
?? Private Bool AnalyzeWScolumns (String ViewSql, String ViewColumn, Out String SourceTable, Out String SourceColumn)
?? {
??? viewsql = viewsql.replace ("/ r / n", string.empty);
??? String sp = "from";
??? String SQL = viewsql.substring (0, viewsql.indexof (sp));
??? sp = "select";
??? SQL = SQL.SUBSTRING (SQL.IndexOf (SP) 6);
/// The above step is to make this SQL "clean", only to analyze the SELECT and FROM. ??? String [] TSQL2 = SQL.SPLIT (','); ??? foreach (string t in tsql2) ??? {???? f (t.indexof (viewcolumn)! = - 1) ?? ?? {????? sql = t; ????? breaf; ????} ???}
The above steps take the string containing the ViewColumn to DBO.YOURTABLEORVIEW.YOURCOLUMN (AS XXX), placed in SQL. The following is divided into three sections by '.'. ??? String [] TSQL3 = SQL.SPLIT ('.'); ??? IF (tsql3.length! = 3)? /// If it is not this three-stage, return (explain some constant columns Ah, wait? ??? {???? sourcetable = string.empty; ???? SourceColumn = String.empty; ???? Return false; ???} ??? Else ??? {?? ?? SourceTable = TSQL3 [1]; ???? IF (TSQL3 [2] .indexof ("as")! = - 1)? // If there is AS, there is an alias ???? {?? ??? sp = "as"; ????? SourceColumn = TSQL3 [2] .remove (tsql3 [2] .indexof (sp), tsql3 [2] .length-tsql3 [2] .indexof (sp)) ; ????} ???? ELSE ???? {????? SourceColumn = TSQL3 [2] .trim (); ????} ???? Return True; ???} ?? }?
OK :)
During the process of studying this problem, I got the help of ZJCXC (Zou Jian) heroes. Thank you here :)
Related posts:
Http://community.9cbs.net/expert/topic/3186/3186099.xml?temp =.5037653
?
