Checking Columns Level Dependencies

Nov 4, 2004

Hi all,
Need a big favour, I have to collect information like
TableName-> it's all columnNames--> dependent table name of EachColumn(where this column is refrenced)

I have managed to get some info like:-

select so.name as 'Table Name',sc.name as 'Column Name',st.[name], sc.length,sc.isnullable,so.crdate
from syscolumns sc
inner join sysobjects so on so.id=sc.id
inner join systypes st on st.xtype= sc.xtype
where so.xtype='U'
order by so.name

sp_depends and sp_MSdependencies is not returning me same fields..

View 1 Replies


ADVERTISEMENT

Checking For Primary Key Dependencies

Jul 20, 2005

Anybody know if there is a system function that can be used from astored procedure that determines if a given primary key has existingdependencies? I want to make a check for this and if there are none, Iwill delete the record. If there are, I will change a field calledbitStatus from 1 to 0. Enterprise Mgr. does something like this underAll Tasks, Display Dependencies. The normal way I do it is to manuallycheck for the existance of the primary key in every dependent table.SQL 2000 server

View 3 Replies View Related

Checking Level Of Security Given The Folder Structure Being Used

Jan 23, 2008

Hi,

Could someone please advise if the report folder structure I am proposing will be secure enough so unauthorised users can't access the reports?

I have created three folders; Departments (that contains subfolders being one for each department), Sections (that contains subfolders being one for each section), and Open Access. A manager will either see the Department or Section folder and then the relevant subfolder for the area he/she manages.

I have deployed the reports from the Development Studio to the Home page, made them invisible there, but created linked reports that then show in the subfolders. Each subfolder contains a separate linked report and each has parameters set within the Properties tab of SSRS that only enables the manager who will access that subfolder to see the report details specific to their area.

A manager will then be given the browser role for only the subfolder he/she has access to.

I would like to be sure that a manager will not be able to see information in other subfolders. Will my proposed structure be secure enough?

Thanks

View 5 Replies View Related

Programmatically Checking The Database Compatibility Level?

Jun 28, 2006

When my app starts up I want to ensure that the database compatibility level has been set to 90. I know about sp_dbcmptlevel, but that only seems to work in an interactive session; the documentation says it can't be used in a stored procedure, and that appears to be true. Does anyone know how I could read the database compatibility level in a stored proc?

View 3 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

Checking Columns In Two Similar Tables

Jan 3, 2014

For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.

There is only one column extra in _dup tables i.e.,idn column.

Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.

View 6 Replies View Related

Checking Columns In Two Similar Tables

Jan 3, 2014

For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.

There is only one column extra in _dup tables i.e.,idn column.

Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.

View 5 Replies View Related

Checking 2 Columns And Displaying Result In 1 Column

Apr 13, 2012

Ihave 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though..the data i want is in columns that have the same name.pretty much what i want to do...is .they also need to be distinct so i dont count duplicates...i can get them as separate tables...but i cant get them together..I need them in 1 column because of how it is sent to the C3 code page and how it reads it...the structure has already been previously set..and there are about 5 other statments that are being executed in this one stored procedure like this (also i wasnt the one who set this up).

image 1 is what is currently set up
imgur: the simple image sharer
top part is what is stored in tables..bottom is more of the result
it basically runs this code to get the bottom

DECLARE @id INT;
DECLARE @invest nvarchar(50);
SET @id = '7633';
SET @invest = '';
SELECT 'a' + CONVERT(nvarchar, orderfindings.risk_rating) AS cat, COUNT(DISTINCT orderfindings.prnt_id) AS stat
FROM orderheader, orderaudits, orderfindings
WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id
AND orderheader.id = @id AND orderfindings.risk_rating > 0 AND orderaudits.Investor_Name LIKE '%' + @invest + '%'
GROUP BY orderfindings.risk_rating

If i want agencies instead of findings..just replace it..agencies and findings are the 2 tables..they are the pretty much identical column wise...but i want the result together..i've tried several ways..but i cant seem to get it.

image 2 - the table at the bottom is more what i'm looking for..it combines them both into 1
imgur: the simple image sharer

if an order has a finding or agency or both in it..then it gets marked as a 1 for that risk rating...if it doesnt..then 0 for that risk rating..and then sum them all up to see what i got..

I've been working with it...did this [SQL] compare2 - Pastebin.com ..got it to display 2 columns..but still not the right result...i'm getting a1 = 1...a2 = 1...so its not running through all the orders...or it needs a way to count it...i put a sum at beginning of case statement..erro because of counts...so i took counts out...

View 7 Replies View Related

Checking Specific Columns Were NULL On Previous Insert (Using If EXISTS)

Aug 3, 2007

Thanks for your time:

Background: After Insert Trigger runs a sproc that inserts values into another table IF items on the form were
populated. THose all work great, but last scenario wont work: Creating a row insert based on Checking that all 22 other items from the prior insert (values of i.columns) were NULL:

IF EXISTS(select DISTINCT i.notes, i.M_Prior, i.M_Worksheet, ...
from inserted i
WHERE i.notes IS NOT NULL AND i.M_Prior = NULL AND i.M_Worksheet = NULL AND...)


BEGIN

Insert into dbo.Items2Fix ...

From inserted i

END

View 1 Replies View Related

Row Level Security Across Multiple Columns

Nov 5, 2005

I need to secure a datawarehouse table at the row level based on 1 tomany keys on that table. A user should only see the rows they haveaccess to. I need to be able to figure out which rows they have accessto using a single sql statement and it cannot be dynamic SQL or astored procedure (this is a limitation based on the reporting tool weuse).The conditions can be any combination of "and" and "or" operators. Ihave seen posts (and actually have three of his books) by Joe celkodescribing disjunctive canonical form. If not familiar please clickthe link below.http://groups.google.com/group/comp...db6abcfa6209d2fA rules table in disjunctive canonical form looks like it mightpotentially be a solution, but I can't figure out how to use it acrossmultiple keys.Here ia an example of what I am trying to do including current tablestructures and sample data. I cannot really change the generic_facttable schema, but the security cross reference table can be modified oraditional tables added.CREATE TABLE generic_fact (generic_fact_key int NOT NULL ,salesrep_key int NOT NULL ,product_key int NOT NULL ,customer_key int NOT NULL ,sales_amount decimal(18, 0) NOT NULL ,CONSTRAINT PK_generic_fact PRIMARY KEY(generic_fact_key))GOinsert into generic_fact values(1,1,10,20,45)insert into generic_fact values(2,1,10,21,90)insert into generic_fact values(3,1,11,22,17)insert into generic_fact values(4,2,10,20,32)insert into generic_fact values(5,2,13,25,6)insert into generic_fact values(6,2,12,24,56)insert into generic_fact values(7,3,11,34,75)CREATE TABLE security_cross_reference (cross_ref_key int NOT NULL ,user_key int NOT NULL ,security_type varchar (50) NOT NULL ,security_value int NOT NULL ,security_operator varchar (20) NOT NULL ,CONSTRAINT PK_security_cross_reference PRIMARY KEY(cross_ref_key))GOinsert into security_cross_reference values(1,1,'product',10,'or')insert into security_cross_reference values(2,1,'customer',21,'or')insert into security_cross_reference values(3,2,'customer',20,'and')insert into security_cross_reference values(4,3,'salesrep',3,'and')insert into security_cross_reference values(5,4,'salesrep',1,'and')insert into security_cross_reference values(6,4,'customer',22,'and')insert into security_cross_reference values(7,4,'product',11,'and')So based on the data in the security cross reference tableUser 1 can see sales for customer 21 or product 10(generic_fact table rows that have generic_fact_key values of 1,2,and4)User 2 can see sales for customer 20(generic_fact table rows that have generic_fact_key values of 1 and 4)User 3 can see sales for salesrep 3(generic_fact table rows that have a generic_fact_key value of 7)User 4 can see sales for salesrep 1 and product 11 and customer 22(generic_fact table rows that have a generic_fact_key value of 3)Does anyone have any ideas on the best way to do this?

View 3 Replies View Related

Reporting Services :: SSRS - How To Hide Columns Based On Drill Down Level

Sep 10, 2015

I thought the built in expression Level() would show me be drill down level. I can't see to get that to work. I'd like to determine my drill down level so I can hide columns based on that.

View 2 Replies View Related

Column Level Or Database Level Encryption/decryption....

Jan 16, 2008

I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256  algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance

View 1 Replies View Related

High Level && Detail Level Design Documents

Nov 19, 2007



Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance

View 1 Replies View Related

Job Dependencies

Aug 13, 2007

Is it possible to make the execution of one job dependent on the successful execution of a previous job?

View 1 Replies View Related

Object Dependencies

Apr 3, 2001

Is there a way for me to gather all the dependencies information on all tables and views at one time?

View 2 Replies View Related

Identifying Dependencies

May 20, 2004

How can get an object's dependencies in SQL Server. For example it I have written a procedure which accesses some tables inside it then the procedure is said to be dependent on that table. Or one procedure might call another procedure and hence dependent.

Can I know an object's dependent objects from any system table. I think Oracle has a table USER_DEPENDENCIES which provides this info (I may be wrong :-().

Can anyone help ?

View 7 Replies View Related

Fix Dependencies During Or After Renaming

Jul 20, 2005

The previous post regarding dependencies reminded me of another bugger I comeup with from time to time. SQL Server allows us to rename objects via asystem stored procedure (I usually use an ADP front-end that presumably callsthe same procedure), but after the rename, I find singnificant strangeness.For instance, triggers are still connected to the tables, but the trigger textstill refers to the old table name. Views and stored procedures dependent onthe table are broken, and are not fixed by even by restarting the server,though I thought the cached plans were supposed to be dumped on shut-down,then recreated on demand later.Is there some tool or technique to rename a table and automatically detect andproperly drop/rebuild dependent triggers, views, stored procedures,relationships, and anything else I'm not thinking of?Oh yeah, and there are extended properties to deal with as well, but I guessthat's intractible unless we know what client build them (e.g. Access ADP),and what it expects to be recorded there, but I suppose it would be nice tojust dump them rather than leave them there possibly corrupted from a client'spersective.

View 4 Replies View Related

SQL Cache Dependencies

May 24, 2007

Hi,


I am currently using SQLCacheDependencies to help me manage my cache in an ASP.NET application that is hosted across a web farm. This utilises the SQL Server 2005 service broker feature.

I have implemented this and is currently working but i have a few questions i want to clear up and i hope someone can assist me in that.


1/. I am starting the broker service by using the command
System.Data.SqlClient.SqlDependency.Start( <connection string> );
I currently have that piece of code running when the application starts in the global.asax file.
What i want to know is will all SQL queries that hit the database register a notification?
Is is possible to only have a selected query register a notification?

In my web application only a handful of database queries require a sqlcachedependency to be created. I dont want all queries to be registering a notification as that will place a burden on my backend server.

1/. So does all queries register a notification once the broker is started?
2/. Is there a way to only tell selected queries to register a notification?
3/. When a cache object is invalidated because of a absolute and slidingscaletimeout, does the existing sqlconnection for that service broker get disposed?

Thanks
Jim

View 4 Replies View Related

Dependencies Of A Table

Aug 7, 2006

Hello,

I have a table in my SQL2000 server and there's a field in this table being created by an object - with a GREEN PLUS sign (look like an APPEND query in MsAccess). How can I locate and edit/modify that (append query-like) object within my SQL 2000 - Enterpise Manager? Here's some more details:

Table: Customer

Field1: CustomerID

Field2: CustomerRegion_ID (contains the ID above & RegionID below)



Table: Region

Field: Region_ID

--------------------------------------------------------------

When I checked on the dependencies of the table "Customer" I saw 1 dependency and this dependency has 2 table depend on it back-to-back (Customer & Region).

I want to modify the "Field2" of the "Customer" table and I looked everywhere on my VB/stored-procedure codes but couldn't find what triggle the creation of the field. The only thing that I suspect that would be the append object with "GREEN PLUS" sign. Any help would be appreciated.

Peter.





View 1 Replies View Related

Table Dependencies

Nov 3, 2006

Hi

Is there any way to know as what are all the entities or objects which are dependent upon a given Table? I wanted to know if i update a particular table in a given database on a server, what are all the other tablesobjects which gets effected because of this updation, may be because of some update triggers defined on this tabl or through cascading updates etc. Any system stored procedure which can help me to know these dependencies.



Many Thanks in Advance!

View 4 Replies View Related

SSIS Dependencies

Mar 8, 2007

Hello Everyone,

I want to run the SSIS service on a few computers. Can I "only" install this service or does this service needs an instance of the SQL server database as well?

* If it does need the database, can the database be shared between various instances of the service or does each one of them need their own database instance?

* are there any particular databases which are needed by SSIS in order to execute? like master, msdb or tempdb?

regards,
Abhishek.

View 5 Replies View Related

Transactions = Table Level Or Row Level ?

Nov 29, 2006

When you utilize transactions in ADO.NET are the locks put on the entire TABLE used or at the row level?
For instance if you do a SELECT within a transaction if you only pull 5 rows out of a 1000 row table can you just make it lock the rows that have been pulled?  It seems like it locks the entire table?
 
Thanks

View 6 Replies View Related

What Is Low Level And High Level Locking

May 3, 2007

Hi, Can anybody please explain me, what is low level and high level locking in SQL Server 2005 database.
Also what is the name of process which converts low level locking into high level locking and vise versa.
-Sanjeev

View 2 Replies View Related

Statement Level Vs. Row Level Triggers

Jul 20, 2005

Hi..I'd very much appreciate it if someone would tell me how to translatea statement level trigger written in Oracle to its equivalent (if there isone)in MS SQL Server. Ditto for a row level trigger.If this is an old topic, I apologize. I'm very much a newbie to SQL Server.Regards,Allan M. Hart

View 2 Replies View Related

Circular Dependencies From Constraints

Jan 6, 2007

Hey!
 I am creating a kind of file browser for an application of mine. The principle is quite straight forward. It consists of folders and files. Each folder can contain other folders and files and so on. The twitch however is that i need a special root entity called site. The site is very much alike a folder but has some other properties. The site can contain folders and files.
To achieve this ive created te following tables (truncated for clearity):
####################            Sites          ##################### ID [Int]                   ## ...                         ####################
####################          Folders         ##################### ID [Int]                   ## SiteID [Int]             ## FolderID [Int]          ## ...                         ####################
####################          Files             ##################### ID [Int]                   ## SiteID [Int]             ## FolderID [Int]          ## ...                         ####################
 Both the folder table and the files table have a check constraint that ensures that either SiteID or FolderID is NULL. They WILL be part of EITHER a folder or a site. Not both!
Then i set up the foreign constraints as follows:Folders.FolderD -> Folders.IDFolders.SiteID -> Sites.IDFiles.FolderID -> Folders.IDFiles.SiteID -> Sites.ID
All constraints have cascade on delete and therefore the last of them cannot be created as it would be circular. (Wich it wont in this case, but theoreticaly its possible)
Iknow WHY this is rendering an error. But how can i work around it? Or would you suggest another design of the tables?

View 2 Replies View Related

How To Get All The Dependencies Of A Stored Procedure?

Mar 5, 2008

Hi All, How to get all the Dependencies of a Stored Procedure?  Dependencies may exists in other databases also. For Example Stored procedure "sp_Employee" exists in the DB "Employee" This SP calling the another SP exists in another DB "Organization" You help would be appreciated.. RegardsGokul 

View 2 Replies View Related

Data Transfer And FK Dependencies

Aug 8, 2001

I want to write a query that lists all table names in the order (based on foreign key dependencies to the other tables) that the tables need to be loaded. Using DTS to transfer the data, the tables seem to be loaded in alphabetical order, which causes the load to fail on databases with heavy use of foreign keys. Can anyone offer some help?

View 2 Replies View Related

View Dependencies Across Containers

May 20, 2008

Hi All

I am trying to get a list of all the dependencies for objects in a database.

I know about sp_depends however many of the objects are not in the catalogue that the object is in and therefore are not shown.

Any help would be useful

--
David

View 4 Replies View Related

Cross Database Dependencies

Jan 3, 2008

As far as I know SQL Server 2005 lists only dependencies within the same database. In many cases though there are objects in one database depending on objects in a different database on the same server. I unsuccessfully looked for a tool to list those and I ended up creating a script to do the job instead. Any improvements are appreciated, e.g. the script uses syscomments which may no longer be available in the next SQL Server version - I have not found a replacement sys.XXX table yet. Please note that you need to use a connection with access to all the SQL databases on a server and that it may take a while to run the script below if you have many databases with numerous objects in there. On the other hand these are exactly the cases when you need to know which of the thousands of objects are dependent on the table or view you are looking at.

P.S. Does anybody know where the extended properties get stored (hint - not in syscomments)? I was looking for those too, since often I document in the description of a field that it links to "DatabaseABC.dbo.TableXYZ.Field1"

Plamen Kouzov

==========
declare @searchstring varchar(50)
declare @dbid int
declare @DBName varchar(1024)
declare @command varchar(1024)
declare @ServerDBs table([dbid] smallint, DBName varchar(1024))
select @searchstring = 'DatabaseABC.dbo.TableXYZ'
insert into @ServerDBs ([dbid], DBName)
select [dbid], [name]
from master.dbo.sysdatabases
where [name] not in ('master','tempdb','model','msdb','pubs','Northwind')

create table #ObjectsFound (DBName varchar(500), ObjectName varchar(500))
while (select count(*) from @ServerDBs)>0
BEGIN
set @dbid = (select TOP 1 dbid from @ServerDBs)
set @DBName = (select DBName from @ServerDBs where dbid = @dbid)
set @command = 'use ' + @DBName + '; insert into #ObjectsFound(DBName, ObjectName) select ''' + @DBName + ''', [name] from sysobjects where id in (select [id] from syscomments where text like ''%' + @searchstring + '%'') order by 1'
exec sp_sqlexec @command
delete from @ServerDBs where dbid = @dbid
END
select * from #ObjectsFound order by 1,2
drop table #ObjectsFound

View 1 Replies View Related

SSIS Dependencies On SQL Deployment

Mar 8, 2007

Sorry to start a new thread, actually Darren answered my first question on SSIS dependencies but I have a clarification and somehow the software of this forum doesn't allow me to post again on my previous thread. (my posts disappears after some time).

My question is that if I choose to deploy the SSIS packages in SQL Server, then SSIS would have a dependency on SQL Database.

Do we know where the deployed packages are stored in SQL. This information is needed so that I can maintain and monitor the DB where the deployed packages are stored.

Also, do you think its a best practice to deploy the packages in SQL ( I think it will be good because then I can have consistent version of a package deployed on many machines... since I store my packages on one consistent DB rather than multiple file folders on each machine).

regards,
Abhishek.

View 1 Replies View Related

File Dependencies At Deployment?

Aug 7, 2006

I have packages with the execute process task. I have included the exe-file from path A using add->existing item to the SSIS-project so that it lies under miscellaneous files. Deploying these packages using the deployment utility the file gets in the deployment folder that I copy to the server. Running the package installer and installing to SQL Server all files get copied to the B path (dtspackages under sql server by default). Trying to run the package however results in complaints about the file not being at location A. I thus changed the path in the execute process task to a relative one and then I get a complaint that the file "is not in path". So how do I do to make sure it finds the executable at location B where it actually is? I thought the deployment utility would handle this on it's own?

View 1 Replies View Related

Drop Table Dependencies!!!!

Mar 6, 2008

Hi folks ...
I am back with another quick question here.
I was working on a table and on my absence it was dropped by one of my collegues. I restored it from the backup. The problem is it is not showing all the stored procedures and other tables under show dependencies as the original table was.
Is it going to affect the way the stored proc. depenendant on it works..or everything will be fine?
If the answer is yes...is there a way I can add the stored procedures or link it to the new table?
Thanks for reading and any insight is more than welcome.
Tanya

View 1 Replies View Related

Help - Tasks That Have Circular Dependencies

Sep 18, 2006

Hello,

I have a package, which calls a sub package to poulate a table depending on a flag in the database (using an ExecuteSQL task to return flagged table name).

The inner package populates some tables, and calculates what needs to be processed next. It sets the next flag.

However, I can't make this work in the control flow, as once the Execute package has completed, I need to start again from the top, as the flag will have changed to the next item.

I hope that I have explained this well enough.

I really need this to work, but SSIS will not let me create a circular dependency. Does anyone know a way around this, or can offer me an alternative solution??

I am getting desperate, so any suggestions will be welcome

Many thanks





View 4 Replies View Related







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