I want find all dependent objects related to a table. I am using -
SELECT DISTINCT so.name
INTO #tmp
FROM dbo.sysobjects so
JOIN dbo.SysComments sc
ON sc.id = so.id
WHERE sc.text LIKE '%tablename%'But, I want all those SP/functions/views that use the output of this query and so on...
Eg.
Table -> used in usp1 -> usp1 used in usp2 ...and so on
I am having a main table and temptable.
Every sunday, new data will be loaded from temptable to main table. I have to make sure that, duplicates does not get loaded from temptable to maintable.
For example, if last sunday a record gets loaded from temp to main. If this sunday also the same record is present then it means that is a duplicate.
The duplicate is decided on below scenario
select 'CodeChanges: ', count(*) from CodeChanges a, CodeChanges_Temp b
where a.AccountNumber = b.AccountNumber and
a.HexaNumber = b.HexaNumber and
a.HexaEffDate = b.HexaEffDate and
a.HexaId = b.HexaId and
[Code] ...
Yesterday (Sunday) , data from temp got loaded onto maintable but with duplicates.
There is a log which just displays number of duplicates.
Yesterday the log displayed 8 duplicates found. I need to find out the 8 duplicates which got loaded yesterday and delete it off from main table.
There is a column in both tables which is 'creation date and time'. Every Sunday when the load happens this column will have that day's date .
Now i need to find out what are all the duplicates which got loaded on this sunday.
The total rows in temp table is : 363
No of duplicates present is : 8
I used below query to find out the duplicates but it is returning all the 363 rows from the maintable instead of the 8 duplicates.
Select 'CodeChanges: ', * from CodeChanges a where
exists
( Select 1 from CodeChanges_Temp b where
a.HexaNumber = b.HexaNumber and
a.HexaEffDate = b.HexaEffDate and
[Code] ...
Need finding the duplicate records which has creation date time as '2015-11-01 00:00:00.000' and all the above columns mentioned in the query matches.
Example
Few colums only metioned below
creationdateandtime HexaNumber HexaCode
1. 1987-10-01 00:00:00.000 5 3
2. 2015-11-01 00:00:00.000 5 3
So here the second record is duplicate. This is what I am trying to find.
How can we monitor the all tables in all databases and send notifications to the team.Is there a way to check to find the no of rows and size of a table last month and find out growth % now
View 4 Replies View RelatedHello,
We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
Regards,
Vnk.
Hello,
We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails in job, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
Regards,
Vnk.
Hi
I am creating a report on a database where some of the table names start
with the @ sign - ie @table1.
Reporting services picks this up as a parameter, instead of a table name in
my query, even though I am encapsulating the table name in square brackets
eg. [@table1]
I have several data sets in the report that i am using to populate valid
parameters. These datasets are all variations of queries from tables that
have @ as the first character.
When i then try to run the report i get an error message as follows:
"The report parameter pool has a default value or a valid value that depends
on the report parameter SD_POOLCONTRACTS. Forward dependencies are not valid"
This is frustrating as SD_POOLCONTRACTS is not a report parameter but one of
the database tables that has @ for it's first character.
here is the query that i use to obtain the valid values for the pool report
parameter that i am trying to set up.
SELECT distinct u_poolcode as Pool
FROM OCRD INNER JOIN
OCRG ON OCRD.GroupCode = OCRG.GroupCode INNER JOIN
CRD1 ON OCRD.CardCode = CRD1.CardCode inner JOIN
[@SD_POOLCONTRACT] INNER JOIN
[@SD_POOLCONTRCT_LINE] ON [@SD_POOLCONTRACT].DocEntry
= [@SD_POOLCONTRCT_LINE].DocEntry INNER JOIN
[@SD_CONTRACTS] ON [@SD_POOLCONTRCT_LINE].U_DocNo =
[@SD_CONTRACTS].DocNum ON
case when len(ocrd.fathercard) = 0 then ocrd.cardcode
else ISNULL(OCRD.FatherCard, OCRD.CardCode) end = [@SD_CONTRACTS].U_CardCode
WHERE (OCRD.CardType = 'c') AND (OCRG.GroupName LIKE N'producer%') AND
(CRD1.AdresType = 'b')
ORDER BY OCRG.GroupName, FatherCard, OCRD.CardCode
Any help would be appreciated.
How do I find out top expensive queries from SQL Server 2008 – Standard edition ?
View 9 Replies View RelatedScript to find a value in all databases ? for example I want to find a value contain word “ SQL” in all databases so I will get the information in which database and which table it exists.
My script is only for finding a value in one database.
I have a query that finds all SPID's connected to a particular database:
select d.name, p.*
from sys.databases d join sys.sysprocesses p
on d.database_id = p.dbid
where d.name = 'my_db'
But now we have a new rule that we should not use outdated compatibility views, and one of them is sys.sysprocesses. I checked sys.dm_exec_connections/session/requests but failed to replace my existing code. The first two don't have dbid, the last one, requests, has it, but it selects only currently executing statements.
I have data like ABC_Tablename_12345, i just want everything before and after _ also if there is no underscore in the name just return the name.
View 2 Replies View RelatedFind unused databases in a instance or when last used or accessed?
I'm on SQL SERVER 2008 R2 64bit -enterprize
I need to find when the databse is last accessed.
I have express edition [advance] of sqlserver 2008 r2 , is it possible to trace every event with out using profiler as u know it does not ship with it.
Basically i want to see how locks are taken and released in each isolation level when query is executed. I am using
SELECT resource_database_id, t.resource_type , t.resource_associated_entity_id,partitions.index_id,blocking_session_id,
indexes.name as index_name,o.name,
t.request_status, t.request_mode,t.request_session_id,
t.resource_description
[code]....
But i am not sure of the order becuase total_elapsed_time is same for many locks
like
resource_typeresource_associated_entity_idrequest_session_idrequest_modeindex_namenameresource_description
DATABASE051SNULLNULL
OBJECT209427886651Sch-SNULLwt
OBJECT209427886652IXNULLwt
PAGE7205759715088793652IXPK_wtNULL1:204815
KEY7205759715088793652XPK_wtNULL(4fb98fd9bfad)
DATABASE052SNULLNULL
Recently I needed to find all processes connected to a particular database, let's call it Test_db. I have a simple query to find all connections to my database:
select *
from sys.databases d join sys.sysprocesses p
on d.database_id = p.dbid
where d.name = 'test_db'
But there was a process that was connected to another database like USE another_db_name; but was actually selecting from tables in test_db. Is it possible to catch such connections?
formula to return patients ages 6 months to 5 years?
create table dbo.TEST
(
MRN varchar(10),
[Code]....
I am getting following error:
"The log in this backup set terminates at LSN 9566000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 986000002731000001 can be restored."
If I am missing to restore previous log backup file, how to find which LSN mapped to which log backup file (.trn file name)? Another word looking at LSN can we tell which log backup file is missing?
In my asp.net project there are about 100 drop down list.I created a table to store data for drop down list in which including [DropdownID],[Order Sequence] and [Description] three columns. The sample like below. Data was input manually by a user. How to code to find out duplicate [OrderSequence]?
DropdownID--OrderSequence--Description
1-------------0--------------AAA
1-------------1--------------BBB
2-------------0--------------YYY
2-------------1--------------XXX
2-------------2--------------QQQ 'DUPLICATE OrderSequence
2-------------2--------------WWW 'DUPLICATE OrderSequence
2-------------3--------------RRR
Is there anyway I can find or make a report if a databases has been accessed/used since last month?
View 4 Replies View RelatedBelow is the code for two data sets and I can't seem to get my head around the issue. I need to find the number of 'ER' visits and 'IN' visits, separately, in dbo.VisitData for the 'Active' patients in dbo.PatientStatus. So, consider patient 69. He is Active on 5/5/2014 but becomes Inactive on 9/15/2014. I only want to count the number of visits ER or IN that are between those dates. In addition if patient 69 becomes active again after 9/15/2014, I need to capture that data as well. Patients can change there status multiple times.
create table dbo.PatientStatus
as
(
patient_id varchar(10),
status_type varchar(10),
status_date datetime
[Code] ....
I have a scenario as below for one ID -
+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 10.00 | 2015-08-25 12:05:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
| 5689 | 130.00 | 2015-08-25 12:07:57.107 | 3 |
+------+--------+-----------------------------+-----+
I want to fetch below 3 records from the above scenario i.e. latest record of each amount (Latest is determined using "descr" column i.e. 4 is greater then 3 -
+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
+------+--------+-----------------------------+-----+
But in case of same amounts I am unable to fetch the latest status as even using partitioning will treat them as one.
CREATE TABLE #TMP
(
ID INT,
AMOUNT DECIMAL,
[DATE] DATETIME,
DESCR VARCHAR(10)
)
INSERT INTO #TMP VALUES
(5689,10.00,'2015-08-25 12:10:57.107','4')
,(5689,10.00,'2015-08-24 12:07:57.107','3')
,(5689,10.00,'2015-08-25 12:05:57.107','3')
,(5689,130.00,'2015-08-24 12:07:57.107','4')
,(5689,130.00,'2015-08-25 12:07:57.107','3')
I have basic knowledge of T-SQL and I am using Cursors to get the first value, the last value and the peak value and some other values from other tables. I found some examples on google but the code I am using is mixed up. I am using multiple Cursors. I need to join three tables to get the result set into the Cursor. The first example uses 2 tables.
@FirstName NVARCHAR,
@LastName NVARCHAR,
@FirstValue decimal,
@HighestValue decimal,
@LastValue decimal
-- First Cursor
DECLARE TESTCURSOR CURSOR
DYNAMIC
[Code] ....
The above code seems totally inefficient but it gives the correct result. Now I want to pull some more value and join a third table (TABLE z) in the above CURSORS and not sure how to make it working using CURSORS.I would like to use the following in the CURSORS above.
SELECT x.publishdate, y.firstname, y.lastname, y.age, z.initialValue AS FirstValue, z.HighestValue AS Highest, z.LastValue AS Last
FROM TABLE x
LEFT OUTER JOIN TABLE y
ON x.id = y.id
INNER JOIN TABLE z
ON x.id = z.id
When I give job Id in filter of this query this will give job status of "Success" but actually my job is currently in executing stage. I want to get all jobs that are currently in executing status.
Use msdb
go
select distinct j.Name as "Job Name", --j.job_id,
case j.enabled
when 1 then 'Enable'
when 0 then 'Disable'
[Code] ....
I have an invoice table with customer, date, sales location, and invoice total. I want to total the invoices by location by customer and see what location sells the most to each customer. My table looks like this.
CustDate LocAmt
A1/1/2014 1520
A1/1/2014 1560
A1/1/2014 2575
A1/1/2014 3580
B1/30/2014 15100
B1/30/2014 15200
B1/30/2014 2575
B1/30/2014 25150
B1/30/2014 35250
I want to get this, because for customer A, location 35 had the largest SUM of sales and for customer B, location 15 had the largest SUM.
CustLoc
A35
B15
If I have to use CTEs to get there, I can do that too. Just a little lost on how to get only the one location per customer.
I see some time email say blocking took place and but it get resolved itself. how to find the history of batch/query which blocked another SPID?
View 3 Replies View RelatedI am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).
How to find particular queries/stored procedures that causing this?
SELECT
P.Publication
,P.Publication_type
,S.Subscriber_ID
,S.Update_Mode
FROM MSPublications P
INNER JOIN MSSubscriptions S
ON P.Publication_ID = S.Publication_ID
give me publication_type=0. So it is transactional replication but how do we know that is pull or push?
Without going to services.msc / configuration manager, is there anyway to know the service account through which SQL server is running?
View 6 Replies View RelatedWe have a process that uses a stored procedure: sp_MysteryProcedure....
The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.
One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).
So my question is multi-part.....
1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?
I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR
Is there anyway,can we find the list of servers by querying at active directory?
View 3 Replies View RelatedHow can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
View 7 Replies View RelatedI need to find all uses of special characters in a database. I used the following code to do this:
USE dbName
GO
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
GO
[code]...
This will check all tables in the database, but if you want to check specific tables you can uncomment the line in the where clause and specify tables to be checked. The query will return any text fields that have any characters other than letters, numbers or spaces.
This code works fine for me because all the tables in my database have single column primary keys. However I know how much Jeff Moden hates cursors or RBAR queries, so my question is could this have been done by any method other than using a cursor?
i would like to know it's possible to find all transaction(insert, delete,update) on a database for a day. if yes what can i do.
View 2 Replies View RelatedWe have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?
View 4 Replies View Related