I have written the Query in which i am getting TableName,Columns,Precision but how can i get Table related Foreign key and Constraints
SELECT DISTINCT
QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'Schema',
QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'Table',
C.NAME AS 'Column',
T.NAME AS 'DataType',
C.max_length,
C.is_nullable,
c.precision,
c.scale
[code]...
Hi,
We have a DB with a heirarchy of tables each connected via Guids and controlled for Delete Operations by Foreign Key Constraints. However, under certain privileged conditions we would like CERTAIN users to be able to perform a Cascade Delete all the way down.
Thus by example we have tables A,B,C,D - Typically once a record is inserted into Table A then we can add multiple records in Table B referencing that new record in Table A...and so on through records in Table C referencing Table B...records in Table D referencing table C. Now when we try and delete that ONE Record in Table A we 'could' implement CASCADE DELETES which would delete all those records in Table B,C,D.
We decided that was too dangerous to implement as Whooosh....all records could be deleted by a non-privileged user deleteing that ONE records in Table A, so we changed the Cascade DELETE to NO ACTION. However, there are times (hah...particularly in Testing !) when we would like a privilegd user to be able to DO that task...i.e. perform a CASCADE delete.
Has anybody got any suggestions on how this would be best, easily and securely implemented.
I have had a brief look at INSTEAD OF Triggers but am not sure about the pitfalls of using this...
ANy help appreciated,
Cheers,
Desmond.
Hi, I want know how can I to build a query to get all the foreignkey contrains exist between tables using the sys tables, for example if the user select this two tables:
dbo.cat_states -> with this fields -> id_state & desc
dbo.cat_universities -> with this fields -> id_state, id_university & desc_university
I want get something like this:
dbo.universities.id_state = dbo.states.id_state
tks 4 help
Leo
Hi All,
I have a table [Vendor] in first database SAMPLEDB. And i have another table [Contract] in another database TESTDB. I need a foreign key in the [Contract] table (which is in TESTDB database) is reference to the other table [Vendor] which is present in the SAMPLEDB.
When i try to creating them through SQL, i received the following error message :
Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key 'SAMPLEDB..VENDOR'.
Is that possible to refer foreign key in external database or not?
If possible, please give solution for this. If not possible, please suggest some alternate way.
Thanks in Advance
Hi,
I have a "master" table that holds the names of data tables (one record in the "master" table for each "data" table).
Can I create a ForeignKey constraint that will prevent the "master" table records from being removed if the cooresponding "data" table exists? Is the way to do this to use INFORMATION_SCHEMA.TABLES as the PrimaryKeyTable for the ForeignKey?
Thanks!
Hi,
I'm trying to figure out how to setup a Constraint (Foreign Key?) from a lookup table.
For the following example, I want to constrain to constrain so that a Car.ParkingSpotNumber is unique for a given ParkingFacility.
Any thoughts?
Thanks!
Andy
Here's the scenario:
CAR table:
Id (PrimaryKey)
LicensePlateNumber
ParkingSpotNumber
PARKINGFACILITY__CAR__LOOKUP table:
Id (PrimaryKey)
CarId (Foreign Key to Car.Id)
ParkingFacilityId (Foreign Key to ParkingFacility)
PARKINGFACILITY table:
Id (PrimaryKey)
PARKINGLOT table:
Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)
Address
PARKINGGARAGE table:
Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)
Address
Here's the T-SQL code to make a SQLServer2005 database for this scenario:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Car]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Car](
[Id] [bigint] NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
[ParkingSpotNumber] [int] NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingFacility](
[Id] [bigint] NOT NULL,
CONSTRAINT [PK_ParkingFacility] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingFacility__To__Car_Lookup](
[Id] [bigint] NOT NULL,
[CarId] [bigint] NOT NULL,
[ParkingFacilityId] [bigint] NOT NULL,
CONSTRAINT [PK_ParkingFacility__To__Car_Lookup] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingLot]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingLot](
[Id] [bigint] NOT NULL,
[Address] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ParkingLot] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingGarage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingGarage](
[Id] [bigint] NOT NULL,
[Address] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ParkingGarage] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_Car]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car] FOREIGN KEY([CarId])
REFERENCES [dbo].[Car] ([Id])
GO
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility] FOREIGN KEY([ParkingFacilityId])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingLot_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingLot]'))
ALTER TABLE [dbo].[ParkingLot] WITH CHECK ADD CONSTRAINT [FK_ParkingLot_ParkingFacility] FOREIGN KEY([Id])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingLot] CHECK CONSTRAINT [FK_ParkingLot_ParkingFacility]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingGarage_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingGarage]'))
ALTER TABLE [dbo].[ParkingGarage] WITH CHECK ADD CONSTRAINT [FK_ParkingGarage_ParkingFacility] FOREIGN KEY([Id])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingGarage] CHECK CONSTRAINT [FK_ParkingGarage_ParkingFacility]
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this?
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Hi,
I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.
i need to develop a stored procedue, in ehich i have to use variable table name.. as Select * from @tableName but i m unable to do so..
it says u need to define @tablename
heres da code
CREATE PROCEDURE validateChildId
(
@childId int,
@tableName varchar(50),
@fid int output
)
AS
(
SELECT @fid=fid FROM @tableName
where @childId= childId
)
if @@rowcount<1
SELECT
@fid = 0
GO
Hi,
i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case?
Thx
Frank
Hi,
I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?
Thanks in advance.
ramesh.p
Does anyone know the syntax to reference a table in a stored procedure using a parameter? I'm trying to make a stored procedure that will "SELECT INTO" a table as the passed parameter. I keep getting an syntax error when I use this:
CREATE PROCEDURE make_table
@tablename varchar(20)
AS
SELECT * FROM old_table
INTO @tablename
I've tried it several ways, but I can't get it to work, and I haven't found any examples of this anywhere. Thanks in advance for any tips.
Hi,
What is the difference between
<Tablename> . . <ColumnName> and <Tablename> . <ColumnName>
this syntax we are using in Storedprocedures.
In SQL 2000, <Tablename> . . <ColumnName> is not working
Eg:
Tablename =a
Column Name = b
Difference between a..b and a.b
Can anyone let me know.
TIA,
Ravi
I know there has already been a thread on this, but I want to push some about it.
In SQL Server, there is a command "SET IDENTITY_INSERT tablename ON".
That allows you to update IDENTITY columns, or do INSERTs that include IDENTITY columns. Although a work-around was given for this in the other thread (reset the IDENTITY seed to the desired value before each INSERT), that is a major pain.
In my database, I have a table that tracks charitable donors. They have a donornum, that is an IDENTITY column, and a year. Together, the donornum and the year form the primary key. Each year end, a copy is made of all donor records in one year, to form the next year's donors. They have to keep the same donornum, but they get a new year value of course. Adding new donors uses the donornum normally, incrementing the IDENTITY donornum value.
The advantage of this is that you can match up one year's donors with the previous year's, by joining on donornum. But I need there to be separate records, so they can have separately updated addresses, annual pledge amounts, etc.
Is there any way the SET IDENTITY_INSERT feature can be added to SQL Everywhere, or some other approach can be found that is less laborious than the existing work-around? (The problem with it is that if you have hundreds of donors to copy, you have to do one ALTER TABLE to reset the identity seed for each donor insert for the new year.)
Thanks.
I use IDENTITY(1,1)
and the help says:
SET IDENTITY_INSERT tablename ON
But if the table is an variable(virtual) table how to make it work?
Like this:
DECLARE @TEMPtable table ( ID int Identity(1,1), invar nvarchar(255)
It does not work with
SET IDENTITY_INSERT @TEMPtable ON
I get this
'Incorrect syntax near '@TEMPtable '
Hi to all , I write the query like belwodeclare @userid nvarchar(20)set @userid ='6,8'print @useridselect * from user_master where user_id in (@userid)Here user_id is int datatype, when i execute this query then meet the error as 6,8Msg 245, Level 16, State 1, Line 4Syntax error converting the nvarchar value '6,8' to a column of data type int. How to do this.? Anybody answer me,...
View 5 Replies View Relatedhi all how are you today i am not good because i can't set this variable in this query please read this thanks
create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go
insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')
insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')
/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column*/
set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )
this query return:
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.
this error returned in second
hi all how are you today i am not good because i can't set this variable in this query please read this thanks
create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go
insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')
insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')
/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column
note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help
*/
set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )
this query return:
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.
this error returned because max(id_no) column is absent
if you are run
select * from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2
this query return that
insqlhelp ---------------
aaa2
(1 row(s) affected)
this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")
other way is
set to any other text variable to table name,
and concetanate to query,
and execute with exec
but in this way you cant set to my int variable
please help me thanks for all
hra
I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns.
I then need to run the following script:
declare Detail_Cursor cursor for
select @colname, max(len(@colname)) from @table
The message I receive is "must declare variable @table".
Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated.
Can anyone let me know what I'm doing wrong or how I can get the same result.
Thanks
Dear all:
I have a storedprocedure for db loader form some temp tables.
my storedprocedure as the following:
declare c cursor
for
select * from @tablename
open c
fetch c into ....
but I get an error for declare cursor for dynamic tablename,
did sql server has some BIF to evaluate the variable for table name?
thanks for your kindly assistance.
regards,
Stanley Huang
Dear all,
Can someone help me with the following function? I would like to use a table name as a variable.
Thanks in advance!
CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar)
RETURNS numeric AS
BEGIN
declare @aantal numeric
if @proces = 'Inhuizen'
begin
if @categorie = 'open_op_tijd'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end
if @categorie = 'open_te_laat'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end
end
return @aantal
END
HiCan someone please shed some light as to how this can be done. With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.select * from @tableName where condition = condition
View 5 Replies View RelatedI have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"?
When I try that is says "Must declare the table variable @Table_Name". Thanks!
Hi Experts,I m using a stored procedure:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[SP_Table_SELECT]( @TableName VarChar)ASset nocount onset ansi_warnings offDECLARE @sql varchar(2000)SET @sql = 'SELECT* FROM (' +@TableName + ')'EXEC (@sql)when I run It it shows return value = o and Query Completed with ErrorsThere are data in My Table. Help Plzzzz!!!!!!
View 7 Replies View RelatedHi all.
I want something like this :
create proc myProc ( @num int ,@name varchar(80))
as
select @num = max(field1) from @name
but there is a problem that the @name is a varchar and the error is :
Server: Msg 170, Level 15, State 1, Procedure sp_myProc, Line 3
Line 3: Incorrect syntax near '@name'.
I tried
select @num = max(field1) from object_id(@name)
and the error was the same
is ther another way than :
exec ("declare @num int select @num = max(field1) insert into anotherTable values( @num) " ) ?
thx
Eyal Peleg
I am trying to create a stored procedure for automating Bulk inserting into tables
for one database to another.
Is there any way i can pass the table name as variable to insert and select stmt
thanks in advance
Hi,
I am new to SQL Server, and this may be a silly problem .. but here goes!
I need to create tables based on the date ie FEB2000 for EOM reports, I thought I may be able to do it by
1) Check if Table Exists, if so fop it
2) Recreate Table
3) Populate it with data
Unfortunately I'm still on step 1 <sad look>
--->
CREATE PROCEDURE TEST AS
DECLARE @TableName varchar(50)
SET @Tablename = 'FEB2000'
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @Tablename)
DROP TABLE @tablename
GO
<---
I would of course hand the table name as a variable, but for testing purposes used a set variable.
Thanks in Advance
Hi All,
I am facing a unique problem, In my DB all tables which has nvarchar datatype columns. When i see any table in EM design mode it shows length of navrchar datatype column correct.
But if i see same table through QA using sp_help tablename
then it will show length of my nvarchar column just double.
when i see all my nvarchar columns in syscolumns it will display the length of my nvarchar columns just double then actual.
i dont know where exactly the problem. Because of that my tester are getting wrong table information through my data dictionary whic i created using sysobects,syscolumns,sysproperties.
can anybody tell where is the problem exaqctly ?
--------------------------------------------------------------------------------
I want to fire a trigger which calls a stored proc, passing the name of the table the trigger is defined on to the proc without having to hardcode.
Can it be done ?
trigger x on table y
for update
as
declare @table_name
select @table_name = get underlying table_name 'y' from somewhere
exec stored proc (@table_name)
where do I find the name of the table the current trigger is defined on ?
thanks
i have a application in which i get "invalid object name "tablename" " sometimes. and its will be fixed if i do a IIS restart.
View 3 Replies View RelatedHello,
I would like to pass into a stored procedure the name of a table. Following code does not work.
USE pubs
GO
CREATE PROC test1
@tableName varchar(40)
AS
SELECT * FROM @tableName
EXEC test1 @tableName='authors'
I would appreciate it very much if someone could help me with this.
Thanks
I have read through BOL but am still confused by the above sql. I think it rebuilds all indexes on a table. Am I correct? (If so, if would seem to be a good thing to run it nightly on all tables in all databases. Or maybe that is too extreme)
Barkingdog
Hi everyone,
I'm using a stored procedure in SQL Server to generate multiple recordsets. The recordset are however generated conditionally. create procedure GetData
(
@a bit
@b bit
)
Select * from Customers
IF(@a=1)
Select * from Products
IF(@b=1)
Select * from Details ------------Issue:The adapter returns the table named as Table, Table1(optional) and Table2(optional). It might be that Table1 is products or Table1 is Details.How can I map my tablename property in this scenario where the sequence of tables is not known.