August 5, 2014 at 12:40 pm
Hi,
Has any one got some useful scripts that I could use that would tell me how a table is populated within a database? So i'm thinking of a stored procedure that will create a temporary table or insert into a table that will tell me if the table is used in the following:
ssis
stored procedure
synonym
function
view
sql job
This would help me loads if possible?
Thanks
August 5, 2014 at 12:46 pm
SQL_Kills (8/5/2014)
Hi,Has any one got some useful scripts that I could you that would tell me how a table is populated within a database? So i'm thinking of a stored procedure that will create a temporary table or insert into a table that will tell me if the table is used in the following:
ssis
stored procedure
synonym
function
view
sql job
This would help me loads if possible?
Thanks
Your question is awfully vague. I think you are asking if there is a way to understand the source of data in your tables? No, this is not possible. You can however, determine if your tables are referenced by any of the things in your list. These all require you to do some digging but none of them are all that difficult to find what base tables are being referenced.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2014 at 12:47 pm
One thing you didn't mention is an external application. What if you have an external application that performs CRUD operations via pass through sql?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2014 at 12:57 pm
Hi,
I'm not sure how my Question is Vague? Ideally the user will enter the table name as a parameter to the stored Procedure findMyTable('TableA') and this will then run numerous sql statments and then output the results to a table showing if they are used within the sql functions I have mentioned. All I am asking is a list of sql scripts for each area of where the table could exists.
Thanks
August 5, 2014 at 12:58 pm
Hi, I do not believe the tables that are in the Database are being updated by an external application.
August 5, 2014 at 1:02 pm
SQL_Kills (8/5/2014)
Hi,I'm not sure how my Question is Vague? Ideally the user will enter the table name as a parameter to the stored Procedure findMyTable('TableA') and this will then run numerous sql statments and then output the results to a table showing if they are used within the sql functions I have mentioned. All I am asking is a list of sql scripts for each area of where the table could exists.
Thanks
Presumably this is NOT an end user type of thing. So really what you are looking for is a utility of some sort to help isolate where a given table is referenced within the server? With the exception of SSIS you can use SQL Search. It is totally free and created by RedGate (the SSC sponsor). Check it out. http://www.red-gate.com/products/sql-development/sql-search/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2014 at 1:04 pm
Quick thought, look in to this sp_describe_first_result_set, does as it says on the tin.
😎
August 5, 2014 at 1:10 pm
Hi, ok the end user will be myself then. I know there is a sql script out there that will return the ssis package name, I think it uses the XML_PATH function. I will have a look at the link you have sent.
Thanks
August 5, 2014 at 1:30 pm
Don't think there is any reason to re-invent the wheel, it's more a matter of rephrasing the question so we can understand what you are after. There are quite few things around, i.e. SMO which allows one to build table creation and insert tsql scripts within an ssis script task, the sp_describe_first_result_set etc. All you have to do is to spell out what you are after, not how you perceive to do it but what you are after.
😎
August 5, 2014 at 2:10 pm
Hi,
So say I have a table called "TableA" and have two database one called "Hub" and the other Database called "Adventure". I then want to call a stored procedure (which you will be able to run on any database on the server) where you will enter the table name as a parameter you want information about..
So the table will have four fields:
database,
tblName,
sourceName,
sourceType,
The result set would be something from the attached file.
August 5, 2014 at 2:16 pm
Right...as I said look at SQL Search. It will do this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2014 at 2:16 pm
To be blunt, that's a lot of manual work you're going to have to do there. There's no magic spell that will figure it out.
The SQL Search tool previously mentioned will get you started, other than that you'll need to analyse the search results and investigate the SSIS packages one by one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2014 at 2:19 pm
SQL_Kills (8/5/2014)
Hi,So say I have a table called "TableA" and have two database one called "Hub" and the other Database called "Adventure". I then want to call a stored procedure (which you will be able to run on any database on the server) where you will enter the table name as a parameter you want information about..
So the table will have four fields:
database,
tblName,
sourceName,
sourceType,
The result set would be something from the attached file.
Think we are moving in the right direction, now can you complete the definition of the following terms:
😎
1. database, (missing how to determine what database, tables are in the scope of a database/schema, one has to define the scope)
2. tblName, Passed as parameter.
3. sourceName, (what does Source mean?)
4. sourceType, (what are the definitions of the types?)
August 5, 2014 at 2:40 pm
1. If you look at the attachment , it is showing blank because when you deploy a ssis package to the server it will stored this to a file i think in the msdb so it wouldn't be stored in any other database?
2. I don't need this field but it will only have the value that you will be passing in as the parameter as that is the table you are searching
3. This is just field name I have picked, it basically tells you the name of of the stored proc, function it uses this table on
4. sourceType (just a name I have given as a fieldName change it if you want to) if you look at the attachment is telling me what this relates to so if the table is in a stored procedure it will say STORED PROCEDURE
August 5, 2014 at 2:49 pm
Just doing a google search and I have found the following that will do some of my searches:
Search Stored Procedure
sp_msforeachdb'use [?]
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
where sm.definition like ''%tableA%'' collate SQL_Latin1_General_CP1_CI_AS
ORDER BY o.type'
Search ssis package
WITH XMLNAMESPACES (
'www.microsoft.com/SqlServer/Dts' AS DTS
,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask
)
SELECT
syspackages.name
,c.value('../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ObjectName
,c.value('(@SQLTask:SqlStatementSource)[1]', 'NVARCHAR(MAX)') AS SqlStatement
FROM ( SELECT folderid, id, name, CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) Package_XML FROM msdb.dbo.sysssispackages) syspackages
CROSS APPLY syspackages.Package_XML.nodes('//DTS:ObjectData//SQLTask:SqlTaskData') s(c)
WHERE c.value('(@SQLTask:SqlStatementSource)[1]', 'NVARCHAR(MAX)') like '%tableA%'
So I just need one for the functions, synonym and views and when there is a match to I either insert into a physical table or create a temp table, which will show me result set as per the attachment "ReverseEngineer.png"
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply