SQL Server 2012 :: Finding Dependencies Of Objects

Dec 27, 2013

I need to do some clean up activities in my databases... So i intended to drop unwanted tables and views.

for that I need to find, whether the table being used by any other objects ?

How could I achieve it?

View 2 Replies


ADVERTISEMENT

Where Are Assembly Dependencies (on SQL Objects) Stored?

Oct 4, 2007



I've created an assembly in Adventureworks, and created a sproc that references a function inside the assembly. All works well with the asssembly and proc.


I know that I can't drop the assembly without first dropping the sproc, but I can't find where the dependency information is stored. sp_depends, sys.sql_dependencies, etc, all come up empty for the assembly, and when I run sp_depends on the SQL sproc object, I get:

"Object does not reference any object, and no objects reference it."


Clearly that's not the case, since if I attempt to drop the assembly, I get the expected error 6590: "DROP ASSEMBLY failed because 'ProcTest' is referenced by object 'uspGetStuffFromAssembly' "


Any idea where this is being stored?

--
Chris Randall
cfrandall@google'smail.com
blogs.ameriteach.com/chris-randall

View 5 Replies View Related

SQL Server 2012 :: Select Data From XML - Objects Within Objects?

Nov 20, 2013

passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.

However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.

Below is a code snippet showing what I have so far.

The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo

DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"

[code]....

View 1 Replies View Related

Finding Modified Objects

Apr 18, 2005

hi,
I want to find the modified database objects(Tables, Proc and Fun) in last 3 days in SQL 2000.

With Regards
Praveen (DBA)

View 4 Replies View Related

SQL 2012 :: Truncating Multiple Tables (with Dependencies)

May 11, 2015

I got a request to truncate some tables on our testing servers.There are only 11 tables and i could go in and truncate them one after the other, but i need a script that i can use to truncate all the tables at a go. The tables also have no dependencies between them. A script to accomplish the same task if the tables had dependencies ...

View 4 Replies View Related

SQL Server 2012 :: List Objects Referenced Outside Of DB

Feb 23, 2015

Need to find the list of objects that are referenced from the outside of current database. Example

Use TestDB1

Create Proc TestProc as begin

select * from TestDB1..Sysobjects

select * from sys.indexes

end

Need a query which able to return the following

LinkedServername, Databasename, TableName

null ,TestDB1 , Sysobjects

View 2 Replies View Related

SQL Server 2012 :: Finding Break In Sequence

Feb 21, 2014

I need to be able to identify breaks in a sequence so I can evaluate the data more correctly. In the sample I have given I need to be able to identify the break in sequence at 69397576, ideally I would set that as a D. My query also needs to recognize that the 3 sequences following 69397576 are sequential and would belong to that set. so the out come would look like this.

id file_name page_follow
693975631555557564_22222221114014810D
693975641555557564_22222221114014810F
693975651555557564_22222221114014810F
693975661555557564_22222221114014810F
693975671555557564_22222221114014810F
693975681555557564_22222221114014810F
693975691555557564_22222221114014810F
693975761555557564_22222221114014810D
693975771555557564_22222221114014810F
693975781555557564_22222221114014810F
693975791555557564_22222221114014810F

here is some test data.
create table test1 (id INT
, [file_name] VARCHAR(100)
, page_follow CHAR(1));
go

[code]....

View 9 Replies View Related

SQL Server 2012 :: Finding Breaks In Key Values?

Jun 12, 2014

The following is sample data I am dealing with.

SELECT * INTO TEMP
FROM
(SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '01-01-2014' AS STARTDATE, '01-31-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '02-01-2014' AS STARTDATE, '02-28-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '03-01-2014' AS STARTDATE, '03-31-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A2000' AS CODE, '04-01-2014' AS STARTDATE, '04-30-2014' AS ENDDATE
UNION
SELECT 'AAAAA' AS CATEGORY, 'A1000' AS CODE, '05-01-2014' AS STARTDATE, '05-31-2014' AS ENDDATE) X

I need to extract the date that the value in CODE column changes to another code for each value of CATEGORY and if there is no change, to record the original CODE value and its startdate for each CATEGORY.

View 3 Replies View Related

SQL Server 2012 :: Finding First And Repeated Values

Aug 26, 2014

I'm trying to come up with a query for this data:

CREATE TABLE #Visits (OpportunityID int, ActivityID int, FirstVisit date, ScheduledEnd datetime, isFirstVisit bit, isRepeatVisit bit)

INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd)
SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL
SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL
SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL

[Code] ....

Here are the expected results:

OpportunityIDActivityIDFirstVisitScheduledEndisFirstVisitisRepeatVisit
110012014-08-172014-08-17 12:00:00.00010
110022014-08-172014-08-17 17:04:13.00001
210032014-08-182014-08-18 20:39:56.00001
210042014-08-182014-08-18 18:00:00.00010

[Code] ....

Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.

I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:

SELECT*,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 1
ELSE 0
END AS isFirstVisit,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 0
ELSE 1
END AS isRepeatVisit
FROM#Visits

View 3 Replies View Related

SQL Server 2012 :: Filter MS Objects Out Of Select Statement

Jul 27, 2015

I am writing a script to get all of the user objects out of a database. What I am having a hard time doing is filtering out the MS replication system objects, such as:

dbo.MSsnapshotdeliveryprogress
dbo.MSreplication_objects
dbo.MSreplication_subscriptions
dbo.MSsavedforeignkeycolumns
dbo.MSsavedforeignkeyextendedproperties
dbo.MSsavedforeignkeys
dbo.MSsnapshotdeliveryprogress
dbo.MSsubscription_agents

View 2 Replies View Related

SQL Server 2012 :: Finding Unused Stored Procedures

Mar 3, 2014

How do you find stale stored procedures ?

In a scenario where a developer created a slight modification of a stored procedure because he was afraid of breaking something else and took the easy way out, and a few more later down the line, multiple versions of a stored proc. doing slightly different things are just laying around.

"Last used" would be useful piece of information to determine the most recent date a stored procedure was called, either by the application itself or by another stored procedure itself called by the application.

Any stored proc not used for more than say 6 months would then be identified as a candidate for clean-up.

So - short or creating - after the fact - a trigger to update the usage date upon each call - which means a lot of work and no result for the next six months, how can one go about this ?

And could this be done in SS2K8 ?

View 5 Replies View Related

How To Write Query For Add Dependencies And Remove Dependencies?

Dec 1, 2007

 I need to write a code for remove dependencies between Table1.Prikey and Table2.Idand  add dependencies between Table1.Prikey and Table3.Id how to write the code? please help .. thank you very much 

View 2 Replies View Related

SQL Server 2012 :: Altering All Objects To Find Syntax Errors

Jul 25, 2014

How to alter all objects in database i want to find if can any syntax errors in my database after restoring from sql 2008 to 2012. I Can create as test and drop them but trying to find a way to alter proc , views and functions..

View 4 Replies View Related

SQL Server 2012 :: Limitation Of Number Of Objects In TempDB Database?

Dec 9, 2014

how to know the limitation of number of objects(Maximum no.of objects allow tempdb database) in a tempdb database?

View 2 Replies View Related

SQL Server 2012 :: Select Statement - Finding Duplicate Records

Feb 18, 2014

I am trying to build a select statement that will

1). find records where a duplicate field exists.
2.) show the ItemNo of records where the Historical data is ' '.

I have the query which shows me the duplicates but I'm stuck when trying to only show records where the Historical field = ' '.

Originally I thought I could accomplish this goal with a sub query based off of the original duplicate result set but SQL server returns an error.

Below you will find a temp table to create and try in your environment.

create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int

[Code] ....

Ultimately I need a result set that returns 'ATMR10 MFZ N', and 'QBGFEP2050 CH DIS'.

View 3 Replies View Related

SQL Server 2012 :: Finding Procedures That Use Declared Table Variables?

Oct 22, 2014

know a way to find all stored procedures that use declared or temp tables, i.e

Declare @temptable TABLE as....

Create table #temptable

View 8 Replies View Related

SQL Server 2012 :: Finding Lowest Level Descendants In Hierarchy

Mar 11, 2015

I've got a fairly large hierarchy table and I'm trying to put together a query to find the lowest level descendants of the hierarchy. I think there must be some way to use the "Breadth-first" approach that's stated in the MSDN technet sites about SQL Server HierarchyID but i'm not sure how to write the necessary T-SQL to traverse that. I know I can get all the descendants of a parent node like this

SELECT *
FROM AdventureWorks2012.HumanResources.Employee
WHERE OrganizationNode.IsDescendantOf(@ParentNode) = 1

However, this query returns all levels for that parent's branch. If I just wanted list of employees that were at the lowest level of the branch(es) for this parent node, how would I do this?

View 1 Replies View Related

SQL Server 2012 :: Finding What Table A User-created Statistics Is Located?

Feb 21, 2014

I can easily find user created stat in a databaseSELECT * FROM DB.sys.stats WHERE user_created=1But how do I determine what tables those stats are in? with over 6000 tables I don't feel like looking through all the tables.

View 2 Replies View Related

SQL Server 2012 :: Finding Duplicates And Show Original / Duplicate Record

Nov 3, 2014

There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.

How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.

SELECT
[groom_first_name]
,[groom_last_name]
,[bride_first_name]
,[bride_last_name]

[code]....

View 5 Replies View Related

SQL 2012 :: Copy All Objects From One Server To Another Server?

Aug 12, 2015

Using import task only can select and copy tables and views.

Since there are many objects at the same DB I don't need copy, is there any way let me select and copy some objects, such as functions, stored procedures?

View 1 Replies View Related

SQL Server 2012 :: Finding Longest String Within A String Field

Mar 20, 2014

We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.

View 9 Replies View Related

SQL 2012 :: View Definition Of Objects?

Sep 8, 2014

I want to create a login with some restriction like the following...

1.I will create a login and ll mapped to a particular DB with the Database Role 'db_datarerader' only,
2.We wants to display the all objects under a DB but we don't want to provide the View Definition to that particular Login.
3.If we Deny the View definition option he can't able to see the Objects which are there under the DB.
4.So My Clear Question is we want to display the Object like tables ,Sps...etc and we don't want to allow him to view the definition of those objects....

View 3 Replies View Related

SQL 2012 :: Use TFS To Maintain Versions Of Objects

Oct 23, 2015

I would like to maintain version control of the all the sql objects (sp, view , tables ) and maintain source code versions control. Any way to use TFS to maintain versions of sql objects. Also the folder structure when using TFS.

View 1 Replies View Related

SQL 2012 :: Finding Lock Escalation Cause

Jun 17, 2015

While running SQL Server Profiler I reached some Lock:Escalations. When I searched for Statements having same SPID as Lock:Escalation event I realized that one of delete statements causes this. Is there any way to find out why lock escalation in such place occurres?

Statement is like:

delete from BOOK_IN_LIBRARY where libraryId in (,,,,); <-20 elements ids

CREATE TABLE BOOK_IN_LIBRARY(
[libraryId] [bigint] NOT NULL,
[bookId] [bigint] NULL,
[otherData] [bigint]NULL,
[otherData2] [int] NULL,

[Code] ....

View 0 Replies View Related

SQL 2012 :: Script Is Not Generating Of Database Objects?

Jun 11, 2014

I have installed SQL Server 2012 Express edition SP1 in my system. I have created a database in the instance. Now when I try to generate scripts of tables using Right click on database > Click on Task > Click on Generate Scripts, it is showing Error in Action 'Getting the list of objects from <database>'.

View 4 Replies View Related

SQL 2012 :: Availability Groups - Missing Objects

Aug 15, 2014

I would like synchronizing all the missing objects (logins, agent jobs, SSIS, and anything else that I've missed) across SQL 2012 Availability Groups.

I would like to be able to able to automate this process....

View 2 Replies View Related

SQL 2012 :: Stored Procedures / Objects Baseline

Jul 7, 2015

Any method on creating baselines for your stored procedures/objects.

View 4 Replies View Related

SQL 2012 :: Listing User Created Objects?

Oct 7, 2015

I'm trying to list everything (tables, view, procedures, functions, etc.) that was created by users in a database.

The query which seems to eliminate the most SQL system type objects is shown below.

SELECT *
FROM sys.all_objects SAO
WHERE SAO.is_ms_shipped = 0
order by SAO.type, SAO.name

This still includes some non-user created objects, like the below. See the attachment for details.

fn_diagramobjects
sp_alterdiagram
sp_creatediagram

How can I get rid of these type of objects without filtering on SAO.name LIKE...

View 4 Replies View Related

SQL 2012 :: Finding Out What Users Are Getting Access Denied

Jun 3, 2014

Is there a query that I can run which will give me a list of users that have tried to use SQL resources for which they do not have permissions?

View 1 Replies View Related

SQL 2012 :: Finding Less Than 10% Revenue Accounts In A Table?

Mar 18, 2015

I'm trying to find out less than 10% in revenue accounts from a table. Below is a snapshot. Basically, I want to add Revenue mix column in the table using procedure.

ACCOUTSREVENUEREVENUEMIX
ACCOUNT1 100 2%
ACCOUNT2 200 4%
ACCOUNT3 500 9%
ACCOUNT4 1000 19%
ACCOUNT5 1500 28%
ACCOUNT6 2000 38%
TOTAL 5300 100%

View 1 Replies View Related

SQL 2012 :: Finding Version Details From IP And Port

Oct 26, 2015

Is it possible to find the version details from IP and Port.

For example I have IP and port number known to me ,but I don't have database credentials and other details.

I want to know which version of Sql server is running.

My purpose is to find the false vulnerabilities in security scanning.

View 1 Replies View Related

SQL 2012 :: Creating A View Which Access Objects In Another Database

Sep 10, 2014

We are running SQL Server 2012 on Windows 2008 Server. In one database, we would like to create a view which access objects in another database without giving the user permissions to the underlying base tables in the other database. The ownership chain is broken in this case. Can this be accomplished (considering the ownership chain is broken)? If so, what is the easiest method to accomplish this task? Or

Example 1 (Works):

In DB1:
--UserA selects from Schema1.View1 (which access tables in DB2).

In DB2:
--UserA exists with select permissions on the base tables accessed by Schema1.View1 (in DB1).

Example 2 (trying to accomplish):

In DB1:
--UserA selects from Schema1.View1 (which access tables in DB2).

In DB2:
--UserA exists (or may not exists) with NO permissions on the base tables accessed by Schema1.View1 (in DB1).

View 1 Replies View Related

SQL 2012 :: 2 Replicated Objects (tables) Not Being Created On Subscriber

Sep 10, 2015

I'm in the process of migrating over nearly 900 reports to a replicated server.

I have moved over 100 reports, stored procedures and their dependent objects so far.

I have two tables that are not being applied to the subscriber.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved