Hello!
I am trying to determine how to differentiate a SQL 2000 and MSDE install
without running a query on the database.
In mmc's post and Andrea's answer on 1/18/05, a clear solution using a query
is noted.
I would like to be able to read a registy value to make the determination if
possible.
Thanks very much for the assistance.
Carl
Hi
Gave you installed SQL Server on one machine, MSDE on another one and looked
at the differences?
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Carl Langren" <test@.test> wrote in message
news:eNlceBWBFHA.3588@.TK2MSFTNGP11.phx.gbl...
> Hello!
> I am trying to determine how to differentiate a SQL 2000 and MSDE install
> without running a query on the database.
> In mmc's post and Andrea's answer on 1/18/05, a clear solution using a
query
> is noted.
> I would like to be able to read a registy value to make the determination
if
> possible.
> Thanks very much for the assistance.
> Carl
>
|||Hi Mike,
Yes, three servers and six workstations. Mixed MSDE, Developer and Full SQL.
I was really looking more for an answer rather than a question......
I found this post:
http://www.masterado.net/home/ng/for...t14419113.aspx
=================================================
11/16/2004 4:27:03 PM SQL Server 2000 registry values by edition?
Is it possible to determine through the registry which edition of SQL Server
2000 has been installed - Enterprise, Standard, Personal, Developer,
Evalulation, Windows CE, Desktop (MSDE)?
I know it's possible using 'SELECT SERVERPROPERTY('edition')' in
QueryAnalyzer but what about the registry?
Thanks!
==================================================
11/28/2004 4:20:00 AM RE: SQL Server 2000 registry values by edition?
No, but you can use registry key below to differentiate between MSDE and
other sql editions:
For a named instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance
Name>\Setup
For a default instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Setup
If one of these keys exist but there is no ProductCode, the instance is one
of the SQL Server 2000 Editions (such as Enterprise, Enterprise Evaluation,
Standard, Developer, or Personal).
If a ProductCode entry is present, the instance is MSDE.
You can also parse the sql errorlog to determine what edition, see KB: INF:
How to Determine the Download Files You Need for Upgrading to SQL Server
2000 Service Pack 3 - http://support.microsoft.com/?id=814618
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||hi Carl,
Carl Langren wrote:
> Hello!
> I am trying to determine how to differentiate a SQL 2000 and MSDE
> install without running a query on the database.
> In mmc's post and Andrea's answer on 1/18/05, a clear solution using
> a query is noted.
> I would like to be able to read a registy value to make the
> determination if possible.
> Thanks very much for the assistance.
> Carl
AFAIK, apart from ProductCode key in the Setup hive you already mentioned,
unfortunately there are no registry difference among SQL Server editions,
and MSDE is one of them, and only directly querying the engine determins a
real answer to this question...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
how to Diff vs Sum in Group By query?
Hello,
if area 'A' contains 2 numbers in 2 rows then
Select area, Sum(number) from tbl1 where area = 'A'
Group By area
gives me the sum of these 2 numbers in area 'A'
But how can I retrieve the difference of these 2 numbers
using T-Sql?
Thanks,
RonRon wrote:
> Hello,
> if area 'A' contains 2 numbers in 2 rows then
> Select area, Sum(number) from tbl1 where area = 'A'
> Group By area
> gives me the sum of these 2 numbers in area 'A'
> But how can I retrieve the difference of these 2 numbers
> using T-Sql?
> Thanks,
> Ron
Max(number) - Min(number)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks. That is pretty
. I forgot to include a
twist in here. I also have a datefld. So num1 may be min
or num may be max. I have to show +num or -num.
select area, (First(num) - Last(num)) as num1 from tbl1
where datefld between '1/1/2005' and 1/2/2005' Group By
area having area = 'A'
I was able to use your trick to get my positive or
negative result using First and Last functions. Any
suggestions appreciated if this is incorrect usage.
Thanks again,
Ron
>--Original Message--
>Ron wrote:
>Max(number) - Min(number)
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed
in my From
>header is my spam trap, so I don't check it very often.
You will get a
>quicker response by posting to the newsgroup.
>
>.
>|||Ron wrote:
> Thanks. That is pretty. I forgot to include a
> twist in here. I also have a datefld. So num1 may be min
> or num may be max. I have to show +num or -num.
> select area, (First(num) - Last(num)) as num1 from tbl1
> where datefld between '1/1/2005' and 1/2/2005' Group By
> area having area = 'A'
> I was able to use your trick to get my positive or
> negative result using First and Last functions. Any
> suggestions appreciated if this is incorrect usage.
First? Last? You must be using Access ... This is a SQL Server group
Do you need a SQL Server (Transact-SQL) solution? Those fnctions do not
exist in T-SQL.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On Fri, 25 Feb 2005 11:19:41 -0800, Ron wrote:
>Thanks. That is pretty. I forgot to include a
>twist in here. I also have a datefld. So num1 may be min
>or num may be max. I have to show +num or -num.
>select area, (First(num) - Last(num)) as num1 from tbl1
>where datefld between '1/1/2005' and 1/2/2005' Group By
>area having area = 'A'
>I was able to use your trick to get my positive or
>negative result using First and Last functions. Any
>suggestions appreciated if this is incorrect usage.
Hi Ron,
Try if this helps:
SELECT G.Area, F.num - L.num AS num1
FROM (SELECT area, MIN(datefld) AS FDate, MAX(datefld) AS LDate
FROM tbl1
WHERE datefld BETWEEN '20050101' AND '20050201'
GROUP BY area) AS G
INNER JOIN tbl1 AS F
ON F.area = G.area
AND F.datefld = G.FDate
INNER JOIN tbl1 AS L
ON L.area = G.area
AND L.datefld = G.LDate
WHERE G.area = 'A'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||SELECT A.area, A.num-B.num
FROM Tbl1 AS A
JOIN Tbl1 AS B
ON A.datefld < B.datefld
AND A.area = 'A'
AND B.area = 'A'
David Portas
SQL Server MVP
--|||Yes, I figured that out. I was writing the sql in Access
and transferring in to Query Analyzer. Sorry bout that.
I did end up creating a udf for first and last.
>--Original Message--
>Ron wrote:
min
>First? Last? You must be using Access ... This is a SQL
Server group
>Do you need a SQL Server (Transact-SQL) solution? Those
fnctions do not
>exist in T-SQL.
>
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed
in my From
>header is my spam trap, so I don't check it very often.
You will get a
>quicker response by posting to the newsgroup.
>
>.
>|||Thanks. I will give that a try.
>--Original Message--
>On Fri, 25 Feb 2005 11:19:41 -0800, Ron wrote:
>
min
>Hi Ron,
>Try if this helps:
>SELECT G.Area, F.num - L.num AS num1
>FROM (SELECT area, MIN(datefld) AS FDate, MAX
(datefld) AS LDate
> FROM tbl1
> WHERE datefld BETWEEN '20050101'
AND '20050201'
> GROUP BY area) AS G
>INNER JOIN tbl1 AS F
> ON F.area = G.area
> AND F.datefld = G.FDate
>INNER JOIN tbl1 AS L
> ON L.area = G.area
> AND L.datefld = G.LDate
>WHERE G.area = 'A'
>
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||Thanks very much for your reply. I will give this a try.
>--Original Message--
>SELECT A.area, A.num-B.num
> FROM Tbl1 AS A
> JOIN Tbl1 AS B
> ON A.datefld < B.datefld
> AND A.area = 'A'
> AND B.area = 'A'
>--
>David Portas
>SQL Server MVP
>--
>.
>
if area 'A' contains 2 numbers in 2 rows then
Select area, Sum(number) from tbl1 where area = 'A'
Group By area
gives me the sum of these 2 numbers in area 'A'
But how can I retrieve the difference of these 2 numbers
using T-Sql?
Thanks,
RonRon wrote:
> Hello,
> if area 'A' contains 2 numbers in 2 rows then
> Select area, Sum(number) from tbl1 where area = 'A'
> Group By area
> gives me the sum of these 2 numbers in area 'A'
> But how can I retrieve the difference of these 2 numbers
> using T-Sql?
> Thanks,
> Ron
Max(number) - Min(number)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks. That is pretty
. I forgot to include atwist in here. I also have a datefld. So num1 may be min
or num may be max. I have to show +num or -num.
select area, (First(num) - Last(num)) as num1 from tbl1
where datefld between '1/1/2005' and 1/2/2005' Group By
area having area = 'A'
I was able to use your trick to get my positive or
negative result using First and Last functions. Any
suggestions appreciated if this is incorrect usage.
Thanks again,
Ron
>--Original Message--
>Ron wrote:
>Max(number) - Min(number)
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed
in my From
>header is my spam trap, so I don't check it very often.
You will get a
>quicker response by posting to the newsgroup.
>
>.
>|||Ron wrote:
> Thanks. That is pretty
. I forgot to include a> twist in here. I also have a datefld. So num1 may be min
> or num may be max. I have to show +num or -num.
> select area, (First(num) - Last(num)) as num1 from tbl1
> where datefld between '1/1/2005' and 1/2/2005' Group By
> area having area = 'A'
> I was able to use your trick to get my positive or
> negative result using First and Last functions. Any
> suggestions appreciated if this is incorrect usage.
First? Last? You must be using Access ... This is a SQL Server group
Do you need a SQL Server (Transact-SQL) solution? Those fnctions do not
exist in T-SQL.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On Fri, 25 Feb 2005 11:19:41 -0800, Ron wrote:
>Thanks. That is pretty
. I forgot to include a>twist in here. I also have a datefld. So num1 may be min
>or num may be max. I have to show +num or -num.
>select area, (First(num) - Last(num)) as num1 from tbl1
>where datefld between '1/1/2005' and 1/2/2005' Group By
>area having area = 'A'
>I was able to use your trick to get my positive or
>negative result using First and Last functions. Any
>suggestions appreciated if this is incorrect usage.
Hi Ron,
Try if this helps:
SELECT G.Area, F.num - L.num AS num1
FROM (SELECT area, MIN(datefld) AS FDate, MAX(datefld) AS LDate
FROM tbl1
WHERE datefld BETWEEN '20050101' AND '20050201'
GROUP BY area) AS G
INNER JOIN tbl1 AS F
ON F.area = G.area
AND F.datefld = G.FDate
INNER JOIN tbl1 AS L
ON L.area = G.area
AND L.datefld = G.LDate
WHERE G.area = 'A'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||SELECT A.area, A.num-B.num
FROM Tbl1 AS A
JOIN Tbl1 AS B
ON A.datefld < B.datefld
AND A.area = 'A'
AND B.area = 'A'
David Portas
SQL Server MVP
--|||Yes, I figured that out. I was writing the sql in Access
and transferring in to Query Analyzer. Sorry bout that.
I did end up creating a udf for first and last.
>--Original Message--
>Ron wrote:
min
>First? Last? You must be using Access ... This is a SQL
Server group
>Do you need a SQL Server (Transact-SQL) solution? Those
fnctions do not
>exist in T-SQL.
>
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed
in my From
>header is my spam trap, so I don't check it very often.
You will get a
>quicker response by posting to the newsgroup.
>
>.
>|||Thanks. I will give that a try.
>--Original Message--
>On Fri, 25 Feb 2005 11:19:41 -0800, Ron wrote:
>
min
>Hi Ron,
>Try if this helps:
>SELECT G.Area, F.num - L.num AS num1
>FROM (SELECT area, MIN(datefld) AS FDate, MAX
(datefld) AS LDate
> FROM tbl1
> WHERE datefld BETWEEN '20050101'
AND '20050201'
> GROUP BY area) AS G
>INNER JOIN tbl1 AS F
> ON F.area = G.area
> AND F.datefld = G.FDate
>INNER JOIN tbl1 AS L
> ON L.area = G.area
> AND L.datefld = G.LDate
>WHERE G.area = 'A'
>
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||Thanks very much for your reply. I will give this a try.
>--Original Message--
>SELECT A.area, A.num-B.num
> FROM Tbl1 AS A
> JOIN Tbl1 AS B
> ON A.datefld < B.datefld
> AND A.area = 'A'
> AND B.area = 'A'
>--
>David Portas
>SQL Server MVP
>--
>.
>
how to diaplay date in MM/dd/yyyy format
how to display date in mm/dd/yy using select query ( i have dates which are in mm/dd/yyyyy format but when i run a query it displaying yyyy-mm-dd format).i want to display date in mm/dd/yyyy format so how to write select query for that
This 'should' work:
SELECT convert( varchar(10), MyColumn, 101 )
For example, using today's date:
SELECT convert( varchar(10), getdate(), 101 )
-
05/25/2007
It the style number which indicates how the system should transform your data.
Here is more detail.
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
|||thanks a lot MVPsqlHow to Determine, how much recordsets returns Query?
Hi all!
Is there any chanse to determine in Transact-SQL, how much recordsets/rows already returned by currently executing query? I don't need count of rows affected by last statement (that @.@.ROWCOUNT returns), but ones, really returned to SQL-Client.
To understand, what I need it for, please see: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1707794&SiteID=1#1715230
Solution found!
See: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1716062&SiteID=1&mode=1#1716062
Wednesday, March 28, 2012
How to determine when the last successful full backup occurred?
Does anyone know how one might, from within a SQL query, determine when the
last successful full backup occurred. I can see this information within
Enterprise Manager (or Management Studio in this case) but I'd like to take
that information and use it to execute a cleanup task to delete all
transaction log backups prior to that last full backup.Look in the backupfile table in msdb database to find the backup
information.
If it is a scheduled job, look in the sysjobhistory table in msdb database.
The run_status value of 1 denotes a successful job completion.
Anith|||here is some code that i use for determining the recent full and differentia
l
backups from the msdb. all you have to do is supply the name of the database
,
and you can use that to get back the pertinent information
select @.backup_set_full = max(bs.backup_set_id)
from msdb.dbo.backupset bs
where bs.database_name = @.dbname
and bs.type = 'D' --for full Database backup
and bs.server_name = @.@.servername
select @.backup_set_diff = max(bs.backup_set_id)
from msdb.dbo.backupset bs
where bs.database_name = @.dbname
and bs.type = 'I' --for latest differential ('I'ncremental)
and bs.backup_set_id > @.backup_set_full
and bs.server_name = @.@.servername
using the backup_set_id, you can then run the following to determine the
physical file name:
select @.physical_device_name_bak=
'"'+convert(varchar(200),bmf.physical_device_name)+'"'
from msdb.dbo.backupset bs, msdb.dbo.backupmediafamily bmf
where bs.media_set_id = bmf.media_set_id
and ((bs.backup_set_id = @.backup_set_full)) -- need full
and bs.server_name = @.@.servername
HTH
Thomas LaRock
Database Administrator
ING Investment Management
"Michael D'Angelo" wrote:
> Does anyone know how one might, from within a SQL query, determine when th
e
> last successful full backup occurred. I can see this information within
> Enterprise Manager (or Management Studio in this case) but I'd like to tak
e
> that information and use it to execute a cleanup task to delete all
> transaction log backups prior to that last full backup.
>
>|||Thanks for the help, I was able to come up with the following query to do
it...
DECLARE @.last datetime
DECLARE @.dbid int
DECLARE @.name nvarchar(255)
DECLARE @.path nvarchar(1024)
CREATE TABLE #temp_db
(name nvarchar(255),
dbid int)
INSERT INTO #temp_db
SELECT name,dbid FROM master..sysdatabases
WHILE ((SELECT COUNT(*) FROM #temp_db) > 0)
BEGIN
SET @.dbid = (SELECT MIN(dbid) FROM #temp_db)
SET @.name = (SELECT name FROM #temp_db WHERE dbid = @.dbid)
SET @.last = (
SELECT CAST(MAX(backup_start_date) as datetime)
FROM backupset
WHERE type = 'D'
AND database_name = @.name)
SET @.path = N'X:\pathtobackupdir' + @.name
PRINT 'Removing data from ' + @.path + ' before ' + CAST(@.last as
nvarchar(1024))
EXECUTE master.dbo.xp_delete_file 0,@.path,N'trn',@.last
DELETE FROM #temp_db WHERE dbid = @.dbid
END
DROP TABLE #temp_db
"Thomas LaRock" <thomas.larock@.discussions.microsoft.com> wrote in message
news:54E3BE74-D1FF-4101-823B-FD75FDB70F8F@.microsoft.com...
> here is some code that i use for determining the recent full and
> differential
> backups from the msdb. all you have to do is supply the name of the
> database,
> and you can use that to get back the pertinent information
> select @.backup_set_full = max(bs.backup_set_id)
> from msdb.dbo.backupset bs
> where bs.database_name = @.dbname
> and bs.type = 'D' --for full Database backup
> and bs.server_name = @.@.servername
> select @.backup_set_diff = max(bs.backup_set_id)
> from msdb.dbo.backupset bs
> where bs.database_name = @.dbname
> and bs.type = 'I' --for latest differential ('I'ncremental)
> and bs.backup_set_id > @.backup_set_full
> and bs.server_name = @.@.servername
>
> using the backup_set_id, you can then run the following to determine the
> physical file name:
> select @.physical_device_name_bak=
> '"'+convert(varchar(200),bmf.physical_device_name)+'"'
> from msdb.dbo.backupset bs, msdb.dbo.backupmediafamily bmf
> where bs.media_set_id = bmf.media_set_id
> and ((bs.backup_set_id = @.backup_set_full)) -- need full
> and bs.server_name = @.@.servername
> HTH
>
> --
> Thomas LaRock
> Database Administrator
> ING Investment Management
>
> "Michael D'Angelo" wrote:
>
last successful full backup occurred. I can see this information within
Enterprise Manager (or Management Studio in this case) but I'd like to take
that information and use it to execute a cleanup task to delete all
transaction log backups prior to that last full backup.Look in the backupfile table in msdb database to find the backup
information.
If it is a scheduled job, look in the sysjobhistory table in msdb database.
The run_status value of 1 denotes a successful job completion.
Anith|||here is some code that i use for determining the recent full and differentia
l
backups from the msdb. all you have to do is supply the name of the database
,
and you can use that to get back the pertinent information
select @.backup_set_full = max(bs.backup_set_id)
from msdb.dbo.backupset bs
where bs.database_name = @.dbname
and bs.type = 'D' --for full Database backup
and bs.server_name = @.@.servername
select @.backup_set_diff = max(bs.backup_set_id)
from msdb.dbo.backupset bs
where bs.database_name = @.dbname
and bs.type = 'I' --for latest differential ('I'ncremental)
and bs.backup_set_id > @.backup_set_full
and bs.server_name = @.@.servername
using the backup_set_id, you can then run the following to determine the
physical file name:
select @.physical_device_name_bak=
'"'+convert(varchar(200),bmf.physical_device_name)+'"'
from msdb.dbo.backupset bs, msdb.dbo.backupmediafamily bmf
where bs.media_set_id = bmf.media_set_id
and ((bs.backup_set_id = @.backup_set_full)) -- need full
and bs.server_name = @.@.servername
HTH
Thomas LaRock
Database Administrator
ING Investment Management
"Michael D'Angelo" wrote:
> Does anyone know how one might, from within a SQL query, determine when th
e
> last successful full backup occurred. I can see this information within
> Enterprise Manager (or Management Studio in this case) but I'd like to tak
e
> that information and use it to execute a cleanup task to delete all
> transaction log backups prior to that last full backup.
>
>|||Thanks for the help, I was able to come up with the following query to do
it...
DECLARE @.last datetime
DECLARE @.dbid int
DECLARE @.name nvarchar(255)
DECLARE @.path nvarchar(1024)
CREATE TABLE #temp_db
(name nvarchar(255),
dbid int)
INSERT INTO #temp_db
SELECT name,dbid FROM master..sysdatabases
WHILE ((SELECT COUNT(*) FROM #temp_db) > 0)
BEGIN
SET @.dbid = (SELECT MIN(dbid) FROM #temp_db)
SET @.name = (SELECT name FROM #temp_db WHERE dbid = @.dbid)
SET @.last = (
SELECT CAST(MAX(backup_start_date) as datetime)
FROM backupset
WHERE type = 'D'
AND database_name = @.name)
SET @.path = N'X:\pathtobackupdir' + @.name
PRINT 'Removing data from ' + @.path + ' before ' + CAST(@.last as
nvarchar(1024))
EXECUTE master.dbo.xp_delete_file 0,@.path,N'trn',@.last
DELETE FROM #temp_db WHERE dbid = @.dbid
END
DROP TABLE #temp_db
"Thomas LaRock" <thomas.larock@.discussions.microsoft.com> wrote in message
news:54E3BE74-D1FF-4101-823B-FD75FDB70F8F@.microsoft.com...
> here is some code that i use for determining the recent full and
> differential
> backups from the msdb. all you have to do is supply the name of the
> database,
> and you can use that to get back the pertinent information
> select @.backup_set_full = max(bs.backup_set_id)
> from msdb.dbo.backupset bs
> where bs.database_name = @.dbname
> and bs.type = 'D' --for full Database backup
> and bs.server_name = @.@.servername
> select @.backup_set_diff = max(bs.backup_set_id)
> from msdb.dbo.backupset bs
> where bs.database_name = @.dbname
> and bs.type = 'I' --for latest differential ('I'ncremental)
> and bs.backup_set_id > @.backup_set_full
> and bs.server_name = @.@.servername
>
> using the backup_set_id, you can then run the following to determine the
> physical file name:
> select @.physical_device_name_bak=
> '"'+convert(varchar(200),bmf.physical_device_name)+'"'
> from msdb.dbo.backupset bs, msdb.dbo.backupmediafamily bmf
> where bs.media_set_id = bmf.media_set_id
> and ((bs.backup_set_id = @.backup_set_full)) -- need full
> and bs.server_name = @.@.servername
> HTH
>
> --
> Thomas LaRock
> Database Administrator
> ING Investment Management
>
> "Michael D'Angelo" wrote:
>
Monday, March 26, 2012
How to determine SQL Server service pack level
I can't remember what command to issue in query analyzer
to see what build the SQL server is at. Can someone help
me? Thanks... (SQL2K)SELECT @.@.Version
If using 2000
select serverproperty('Productversion') --major minor build level
select serverproperty('Productlevel')-- SP
select serverproperty('edition') --edition of SQL Server
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||http://www.aspfaq.com/2160
"Brandon" <nospam@.noway.com> wrote in message
news:09ca01c3632b$cfb5d650$a401280a@.phx.gbl...
> I can't remember what command to issue in query analyzer
> to see what build the SQL server is at. Can someone help
> me? Thanks... (SQL2K)|||Fantastic! Thank you!
>--Original Message--
>SELECT @.@.Version
>If using 2000
>select serverproperty('Productversion') --major minor
build level
>select serverproperty('Productlevel')-- SP
>select serverproperty('edition') --edition of SQL Server
>--
>
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>.
>
to see what build the SQL server is at. Can someone help
me? Thanks... (SQL2K)SELECT @.@.Version
If using 2000
select serverproperty('Productversion') --major minor build level
select serverproperty('Productlevel')-- SP
select serverproperty('edition') --edition of SQL Server
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||http://www.aspfaq.com/2160
"Brandon" <nospam@.noway.com> wrote in message
news:09ca01c3632b$cfb5d650$a401280a@.phx.gbl...
> I can't remember what command to issue in query analyzer
> to see what build the SQL server is at. Can someone help
> me? Thanks... (SQL2K)|||Fantastic! Thank you!
>--Original Message--
>SELECT @.@.Version
>If using 2000
>select serverproperty('Productversion') --major minor
build level
>select serverproperty('Productlevel')-- SP
>select serverproperty('edition') --edition of SQL Server
>--
>
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>.
>
How to determine licensing mode and number of licenses in SQL 2005
I'm trying to find out how my SQL 2005 server's licensing is set up. I
don't see it in the properties of the server and when I query
serverproperties, it says licenseType returns disabled and NumLicenses
returns NULL. In SQL 2000, there was a Control Panel applet, but it's not
there for SQL 2005.
Thanks for your help!Hi
This was an old problem with SQL2000 prior to SP2
http://support.microsoft.com/?kbid=291332
If you are using SQL 2005 Developer edition then you may not have a license
type as these don't support standard licencing e.g.
SELECT
CAST( SERVERPROPERTY('productversion') AS varchar(15)) AS productversion,
CAST( SERVERPROPERTY ('productlevel') AS varchar(15)) AS productlevel,
CAST( SERVERPROPERTY ('edition') AS varchar(20)) AS edition,
CAST( SERVERPROPERTY ('licensetype') AS varchar(15)) AS licensetype,
CAST( SERVERPROPERTY ('numlicenses') AS varchar(15)) AS Numlicenses
productversion productlevel edition licensetype
Numlicenses
-- -- -- --
--
9.00.2047.00 SP1 Developer Edition DISABLED NULL
John
"Nieves" wrote:
> I'm trying to find out how my SQL 2005 server's licensing is set up. I
> don't see it in the properties of the server and when I query
> serverproperties, it says licenseType returns disabled and NumLicenses
> returns NULL. In SQL 2000, there was a Control Panel applet, but it's not
> there for SQL 2005.
> Thanks for your help!
>
>
don't see it in the properties of the server and when I query
serverproperties, it says licenseType returns disabled and NumLicenses
returns NULL. In SQL 2000, there was a Control Panel applet, but it's not
there for SQL 2005.
Thanks for your help!Hi
This was an old problem with SQL2000 prior to SP2
http://support.microsoft.com/?kbid=291332
If you are using SQL 2005 Developer edition then you may not have a license
type as these don't support standard licencing e.g.
SELECT
CAST( SERVERPROPERTY('productversion') AS varchar(15)) AS productversion,
CAST( SERVERPROPERTY ('productlevel') AS varchar(15)) AS productlevel,
CAST( SERVERPROPERTY ('edition') AS varchar(20)) AS edition,
CAST( SERVERPROPERTY ('licensetype') AS varchar(15)) AS licensetype,
CAST( SERVERPROPERTY ('numlicenses') AS varchar(15)) AS Numlicenses
productversion productlevel edition licensetype
Numlicenses
-- -- -- --
--
9.00.2047.00 SP1 Developer Edition DISABLED NULL
John
"Nieves" wrote:
> I'm trying to find out how my SQL 2005 server's licensing is set up. I
> don't see it in the properties of the server and when I query
> serverproperties, it says licenseType returns disabled and NumLicenses
> returns NULL. In SQL 2000, there was a Control Panel applet, but it's not
> there for SQL 2005.
> Thanks for your help!
>
>
Friday, March 23, 2012
How to determine actual constraint name by passing a column name
I need a query in which I can pass in a column name and table name and will
be provided all constraints ( all types ) for that given column in that
table. I've created and found several queries that work for various types
but have not found one that does what I need for Unique constraints. I have
the following for FKs, but it doesn't work for UQs
select db_name() as DATABASE_name
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid) as OWNER
,c_obj.name as CONSTRAINT_NAME
,col.name as COLUMN_NAME
,col.colid as ORDINAL_POSITION
,c_obj.xtype as XTYPE
from
sysobjects c_obj
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id and con.colid = col.colid
where
c_obj.xtype = 'F'
ORDER BY t_obj.name
If anyone can point out what I am missing, I'd really appreciate it.
Thanks
RachelDid you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN_USAGE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
>I need a query in which I can pass in a column name and table name and will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Yes, but that does not provide information on Unique Constraints ( UQ)
"Tibor Karaszi" wrote:
> Did you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN_USAGE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> >I need a query in which I can pass in a column name and table name and will
> > be provided all constraints ( all types ) for that given column in that
> > table. I've created and found several queries that work for various types
> > but have not found one that does what I need for Unique constraints. I have
> > the following for FKs, but it doesn't work for UQs
> > select db_name() as DATABASE_name
> > ,t_obj.name as TABLE_NAME
> > ,user_name(c_obj.uid) as OWNER
> > ,c_obj.name as CONSTRAINT_NAME
> > ,col.name as COLUMN_NAME
> > ,col.colid as ORDINAL_POSITION
> > ,c_obj.xtype as XTYPE
> > from
> > sysobjects c_obj
> > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > join sysconstraints con on c_obj.id = con.constid
> > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > where
> > c_obj.xtype = 'F'
> > ORDER BY t_obj.name
> >
> > If anyone can point out what I am missing, I'd really appreciate it.
> > Thanks
> > Rachel
>
>|||> Yes, but that does not provide information on Unique Constraints ( UQ)
Really? Try this repro, and let us know how it works out for you.
USE tempdb
GO
CREATE TABLE dbo.foobar
(
foo INT NOT NULL UNIQUE,
bar VARCHAR(12)
)
ALTER TABLE dbo.foobar ADD CONSTRAINT UQ_Bar UNIQUE(bar)
GO
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME='foobar' AND COLUMN_NAME IN ('foo','bar')
GO
DROP TABLE dbo.foobar
GO
http://www.aspfaq.com/
(Reverse address to reply.)|||You need to do a little more work, but the starting point is the view
mentioned by Tibor. Logically, you need to determine if the column is
associated with a constraint and if the associated constraint is a unique
constraint. Constraint information can be found in the TABLE_CONSTRAINTS
view. hint - looks like a join is involved. BTW - what if unique-ness is
enforced with an index and not a constraint?
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:7CD67018-4028-4823-8320-26D90DB84B62@.microsoft.com...
> Yes, but that does not provide information on Unique Constraints ( UQ)
> "Tibor Karaszi" wrote:
> > Did you try the INFORMATION_SCHEMA views? For instance
CONSTRAINT_COLUMN_USAGE.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> > news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> > >I need a query in which I can pass in a column name and table name and
will
> > > be provided all constraints ( all types ) for that given column in
that
> > > table. I've created and found several queries that work for various
types
> > > but have not found one that does what I need for Unique constraints.
I have
> > > the following for FKs, but it doesn't work for UQs
> > > select db_name() as DATABASE_name
> > > ,t_obj.name as TABLE_NAME
> > > ,user_name(c_obj.uid) as OWNER
> > > ,c_obj.name as CONSTRAINT_NAME
> > > ,col.name as COLUMN_NAME
> > > ,col.colid as ORDINAL_POSITION
> > > ,c_obj.xtype as XTYPE
> > > from
> > > sysobjects c_obj
> > > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > > join sysconstraints con on c_obj.id = con.constid
> > > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > > where
> > > c_obj.xtype = 'F'
> > > ORDER BY t_obj.name
> > >
> > > If anyone can point out what I am missing, I'd really appreciate it.
> > > Thanks
> > > Rachel
> >
> >
> >|||Ken,
Your query will not pick up unique and primary key constraints - they have
no data in syscolumns. To "fix" your query, make the join with syscolumns a
LEFT join:
> LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
Now, however, your 'Column' column will be null for PK and UQ constraints,
because they are implemented as indexes and their columns are stored in
sysindexes, not sysconstraints.
(BTW, the INFORMATION_SCHEMA views will not return any data regarding
default constraints, so I think you're on a better track by going straight
to the system tables.)
Basically, your start is correct in that
select *
from sysobjects
where parent_obj = object_id('<tablename>')
will return all of a table's constraints.
Since you want column information for each constraint, you're also going to
have problems when a constraint (PK, UQ, FK) covers more than one of a
table's columns. You need to decide whether to have multiple column
constraints come back as multiple rows or as a comma-delimited string (like
sp_helpconstraint or sp_helpindex do)
Do you like the output of sp_helpconstraint? If so, I would recommend you
just rewrite sp_helpconstraint, modifying it to take a table name and owner
as parameters, add the appropriate filter, and make it return only one
result set and just the resulting columns you want. Be sure to test on
multiple-column constraints.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> I need a query in which I can pass in a column name and table name and
will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I
have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Of course I meant rewrite sp_helpconstraint as a NEW stored procedure, with
a different name!
Ron
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23GUlV7U7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Ken,
> Your query will not pick up unique and primary key constraints - they have
> no data in syscolumns. To "fix" your query, make the join with syscolumns
a
> LEFT join:
> > LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> Now, however, your 'Column' column will be null for PK and UQ constraints,
> because they are implemented as indexes and their columns are stored in
> sysindexes, not sysconstraints.
> (BTW, the INFORMATION_SCHEMA views will not return any data regarding
> default constraints, so I think you're on a better track by going straight
> to the system tables.)
> Basically, your start is correct in that
> select *
> from sysobjects
> where parent_obj = object_id('<tablename>')
> will return all of a table's constraints.
> Since you want column information for each constraint, you're also going
to
> have problems when a constraint (PK, UQ, FK) covers more than one of a
> table's columns. You need to decide whether to have multiple column
> constraints come back as multiple rows or as a comma-delimited string
(like
> sp_helpconstraint or sp_helpindex do)
> Do you like the output of sp_helpconstraint? If so, I would recommend you
> just rewrite sp_helpconstraint, modifying it to take a table name and
owner
> as parameters, add the appropriate filter, and make it return only one
> result set and just the resulting columns you want. Be sure to test on
> multiple-column constraints.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> > I need a query in which I can pass in a column name and table name and
> will
> > be provided all constraints ( all types ) for that given column in that
> > table. I've created and found several queries that work for various
types
> > but have not found one that does what I need for Unique constraints. I
> have
> > the following for FKs, but it doesn't work for UQs
> > select db_name() as DATABASE_name
> > ,t_obj.name as TABLE_NAME
> > ,user_name(c_obj.uid) as OWNER
> > ,c_obj.name as CONSTRAINT_NAME
> > ,col.name as COLUMN_NAME
> > ,col.colid as ORDINAL_POSITION
> > ,c_obj.xtype as XTYPE
> > from
> > sysobjects c_obj
> > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > join sysconstraints con on c_obj.id = con.constid
> > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > where
> > c_obj.xtype = 'F'
> > ORDER BY t_obj.name
> >
> > If anyone can point out what I am missing, I'd really appreciate it.
> > Thanks
> > Rachel
>
be provided all constraints ( all types ) for that given column in that
table. I've created and found several queries that work for various types
but have not found one that does what I need for Unique constraints. I have
the following for FKs, but it doesn't work for UQs
select db_name() as DATABASE_name
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid) as OWNER
,c_obj.name as CONSTRAINT_NAME
,col.name as COLUMN_NAME
,col.colid as ORDINAL_POSITION
,c_obj.xtype as XTYPE
from
sysobjects c_obj
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id and con.colid = col.colid
where
c_obj.xtype = 'F'
ORDER BY t_obj.name
If anyone can point out what I am missing, I'd really appreciate it.
Thanks
RachelDid you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN_USAGE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
>I need a query in which I can pass in a column name and table name and will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Yes, but that does not provide information on Unique Constraints ( UQ)
"Tibor Karaszi" wrote:
> Did you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN_USAGE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> >I need a query in which I can pass in a column name and table name and will
> > be provided all constraints ( all types ) for that given column in that
> > table. I've created and found several queries that work for various types
> > but have not found one that does what I need for Unique constraints. I have
> > the following for FKs, but it doesn't work for UQs
> > select db_name() as DATABASE_name
> > ,t_obj.name as TABLE_NAME
> > ,user_name(c_obj.uid) as OWNER
> > ,c_obj.name as CONSTRAINT_NAME
> > ,col.name as COLUMN_NAME
> > ,col.colid as ORDINAL_POSITION
> > ,c_obj.xtype as XTYPE
> > from
> > sysobjects c_obj
> > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > join sysconstraints con on c_obj.id = con.constid
> > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > where
> > c_obj.xtype = 'F'
> > ORDER BY t_obj.name
> >
> > If anyone can point out what I am missing, I'd really appreciate it.
> > Thanks
> > Rachel
>
>|||> Yes, but that does not provide information on Unique Constraints ( UQ)
Really? Try this repro, and let us know how it works out for you.
USE tempdb
GO
CREATE TABLE dbo.foobar
(
foo INT NOT NULL UNIQUE,
bar VARCHAR(12)
)
ALTER TABLE dbo.foobar ADD CONSTRAINT UQ_Bar UNIQUE(bar)
GO
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME='foobar' AND COLUMN_NAME IN ('foo','bar')
GO
DROP TABLE dbo.foobar
GO
http://www.aspfaq.com/
(Reverse address to reply.)|||You need to do a little more work, but the starting point is the view
mentioned by Tibor. Logically, you need to determine if the column is
associated with a constraint and if the associated constraint is a unique
constraint. Constraint information can be found in the TABLE_CONSTRAINTS
view. hint - looks like a join is involved. BTW - what if unique-ness is
enforced with an index and not a constraint?
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:7CD67018-4028-4823-8320-26D90DB84B62@.microsoft.com...
> Yes, but that does not provide information on Unique Constraints ( UQ)
> "Tibor Karaszi" wrote:
> > Did you try the INFORMATION_SCHEMA views? For instance
CONSTRAINT_COLUMN_USAGE.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> > news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> > >I need a query in which I can pass in a column name and table name and
will
> > > be provided all constraints ( all types ) for that given column in
that
> > > table. I've created and found several queries that work for various
types
> > > but have not found one that does what I need for Unique constraints.
I have
> > > the following for FKs, but it doesn't work for UQs
> > > select db_name() as DATABASE_name
> > > ,t_obj.name as TABLE_NAME
> > > ,user_name(c_obj.uid) as OWNER
> > > ,c_obj.name as CONSTRAINT_NAME
> > > ,col.name as COLUMN_NAME
> > > ,col.colid as ORDINAL_POSITION
> > > ,c_obj.xtype as XTYPE
> > > from
> > > sysobjects c_obj
> > > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > > join sysconstraints con on c_obj.id = con.constid
> > > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > > where
> > > c_obj.xtype = 'F'
> > > ORDER BY t_obj.name
> > >
> > > If anyone can point out what I am missing, I'd really appreciate it.
> > > Thanks
> > > Rachel
> >
> >
> >|||Ken,
Your query will not pick up unique and primary key constraints - they have
no data in syscolumns. To "fix" your query, make the join with syscolumns a
LEFT join:
> LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
Now, however, your 'Column' column will be null for PK and UQ constraints,
because they are implemented as indexes and their columns are stored in
sysindexes, not sysconstraints.
(BTW, the INFORMATION_SCHEMA views will not return any data regarding
default constraints, so I think you're on a better track by going straight
to the system tables.)
Basically, your start is correct in that
select *
from sysobjects
where parent_obj = object_id('<tablename>')
will return all of a table's constraints.
Since you want column information for each constraint, you're also going to
have problems when a constraint (PK, UQ, FK) covers more than one of a
table's columns. You need to decide whether to have multiple column
constraints come back as multiple rows or as a comma-delimited string (like
sp_helpconstraint or sp_helpindex do)
Do you like the output of sp_helpconstraint? If so, I would recommend you
just rewrite sp_helpconstraint, modifying it to take a table name and owner
as parameters, add the appropriate filter, and make it return only one
result set and just the resulting columns you want. Be sure to test on
multiple-column constraints.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> I need a query in which I can pass in a column name and table name and
will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I
have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Of course I meant rewrite sp_helpconstraint as a NEW stored procedure, with
a different name!
Ron
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23GUlV7U7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Ken,
> Your query will not pick up unique and primary key constraints - they have
> no data in syscolumns. To "fix" your query, make the join with syscolumns
a
> LEFT join:
> > LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> Now, however, your 'Column' column will be null for PK and UQ constraints,
> because they are implemented as indexes and their columns are stored in
> sysindexes, not sysconstraints.
> (BTW, the INFORMATION_SCHEMA views will not return any data regarding
> default constraints, so I think you're on a better track by going straight
> to the system tables.)
> Basically, your start is correct in that
> select *
> from sysobjects
> where parent_obj = object_id('<tablename>')
> will return all of a table's constraints.
> Since you want column information for each constraint, you're also going
to
> have problems when a constraint (PK, UQ, FK) covers more than one of a
> table's columns. You need to decide whether to have multiple column
> constraints come back as multiple rows or as a comma-delimited string
(like
> sp_helpconstraint or sp_helpindex do)
> Do you like the output of sp_helpconstraint? If so, I would recommend you
> just rewrite sp_helpconstraint, modifying it to take a table name and
owner
> as parameters, add the appropriate filter, and make it return only one
> result set and just the resulting columns you want. Be sure to test on
> multiple-column constraints.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> > I need a query in which I can pass in a column name and table name and
> will
> > be provided all constraints ( all types ) for that given column in that
> > table. I've created and found several queries that work for various
types
> > but have not found one that does what I need for Unique constraints. I
> have
> > the following for FKs, but it doesn't work for UQs
> > select db_name() as DATABASE_name
> > ,t_obj.name as TABLE_NAME
> > ,user_name(c_obj.uid) as OWNER
> > ,c_obj.name as CONSTRAINT_NAME
> > ,col.name as COLUMN_NAME
> > ,col.colid as ORDINAL_POSITION
> > ,c_obj.xtype as XTYPE
> > from
> > sysobjects c_obj
> > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > join sysconstraints con on c_obj.id = con.constid
> > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > where
> > c_obj.xtype = 'F'
> > ORDER BY t_obj.name
> >
> > If anyone can point out what I am missing, I'd really appreciate it.
> > Thanks
> > Rachel
>
Wednesday, March 21, 2012
How to determine actual constraint name by passing a column name
I need a query in which I can pass in a column name and table name and will
be provided all constraints ( all types ) for that given column in that
table. I've created and found several queries that work for various types
but have not found one that does what I need for Unique constraints. I have
the following for FKs, but it doesn't work for UQs
select db_name() as DATABASE_name
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid) as OWNER
,c_obj.name as CONSTRAINT_NAME
,col.name as COLUMN_NAME
,col.colid as ORDINAL_POSITION
,c_obj.xtype as XTYPE
from
sysobjects c_obj
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id and con.colid = col.colid
where
c_obj.xtype = 'F'
ORDER BY t_obj.name
If anyone can point out what I am missing, I'd really appreciate it.
Thanks
RachelDid you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN
_USAGE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
>I need a query in which I can pass in a column name and table name and will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I ha
ve
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Ken,
Your query will not pick up unique and primary key constraints - they have
no data in syscolumns. To "fix" your query, make the join with syscolumns a
LEFT join:
> LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
Now, however, your 'Column' column will be null for PK and UQ constraints,
because they are implemented as indexes and their columns are stored in
sysindexes, not sysconstraints.
(BTW, the INFORMATION_SCHEMA views will not return any data regarding
default constraints, so I think you're on a better track by going straight
to the system tables.)
Basically, your start is correct in that
select *
from sysobjects
where parent_obj = object_id('<tablename>')
will return all of a table's constraints.
Since you want column information for each constraint, you're also going to
have problems when a constraint (PK, UQ, FK) covers more than one of a
table's columns. You need to decide whether to have multiple column
constraints come back as multiple rows or as a comma-delimited string (like
sp_helpconstraint or sp_helpindex do)
Do you like the output of sp_helpconstraint? If so, I would recommend you
just rewrite sp_helpconstraint, modifying it to take a table name and owner
as parameters, add the appropriate filter, and make it return only one
result set and just the resulting columns you want. Be sure to test on
multiple-column constraints.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> I need a query in which I can pass in a column name and table name and
will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I
have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Of course I meant rewrite sp_helpconstraint as a NEW stored procedure, with
a different name!
Ron
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23GUlV7U7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Ken,
> Your query will not pick up unique and primary key constraints - they have
> no data in syscolumns. To "fix" your query, make the join with syscolumns
a
> LEFT join:
> Now, however, your 'Column' column will be null for PK and UQ constraints,
> because they are implemented as indexes and their columns are stored in
> sysindexes, not sysconstraints.
> (BTW, the INFORMATION_SCHEMA views will not return any data regarding
> default constraints, so I think you're on a better track by going straight
> to the system tables.)
> Basically, your start is correct in that
> select *
> from sysobjects
> where parent_obj = object_id('<tablename>')
> will return all of a table's constraints.
> Since you want column information for each constraint, you're also going
to
> have problems when a constraint (PK, UQ, FK) covers more than one of a
> table's columns. You need to decide whether to have multiple column
> constraints come back as multiple rows or as a comma-delimited string
(like
> sp_helpconstraint or sp_helpindex do)
> Do you like the output of sp_helpconstraint? If so, I would recommend you
> just rewrite sp_helpconstraint, modifying it to take a table name and
owner
> as parameters, add the appropriate filter, and make it return only one
> result set and just the resulting columns you want. Be sure to test on
> multiple-column constraints.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> will
types[vbcol=seagreen]
> have
>
be provided all constraints ( all types ) for that given column in that
table. I've created and found several queries that work for various types
but have not found one that does what I need for Unique constraints. I have
the following for FKs, but it doesn't work for UQs
select db_name() as DATABASE_name
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid) as OWNER
,c_obj.name as CONSTRAINT_NAME
,col.name as COLUMN_NAME
,col.colid as ORDINAL_POSITION
,c_obj.xtype as XTYPE
from
sysobjects c_obj
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id and con.colid = col.colid
where
c_obj.xtype = 'F'
ORDER BY t_obj.name
If anyone can point out what I am missing, I'd really appreciate it.
Thanks
RachelDid you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN
_USAGE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
>I need a query in which I can pass in a column name and table name and will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I ha
ve
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Ken,
Your query will not pick up unique and primary key constraints - they have
no data in syscolumns. To "fix" your query, make the join with syscolumns a
LEFT join:
> LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
Now, however, your 'Column' column will be null for PK and UQ constraints,
because they are implemented as indexes and their columns are stored in
sysindexes, not sysconstraints.
(BTW, the INFORMATION_SCHEMA views will not return any data regarding
default constraints, so I think you're on a better track by going straight
to the system tables.)
Basically, your start is correct in that
select *
from sysobjects
where parent_obj = object_id('<tablename>')
will return all of a table's constraints.
Since you want column information for each constraint, you're also going to
have problems when a constraint (PK, UQ, FK) covers more than one of a
table's columns. You need to decide whether to have multiple column
constraints come back as multiple rows or as a comma-delimited string (like
sp_helpconstraint or sp_helpindex do)
Do you like the output of sp_helpconstraint? If so, I would recommend you
just rewrite sp_helpconstraint, modifying it to take a table name and owner
as parameters, add the appropriate filter, and make it return only one
result set and just the resulting columns you want. Be sure to test on
multiple-column constraints.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> I need a query in which I can pass in a column name and table name and
will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I
have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Of course I meant rewrite sp_helpconstraint as a NEW stored procedure, with
a different name!
Ron
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23GUlV7U7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Ken,
> Your query will not pick up unique and primary key constraints - they have
> no data in syscolumns. To "fix" your query, make the join with syscolumns
a
> LEFT join:
> Now, however, your 'Column' column will be null for PK and UQ constraints,
> because they are implemented as indexes and their columns are stored in
> sysindexes, not sysconstraints.
> (BTW, the INFORMATION_SCHEMA views will not return any data regarding
> default constraints, so I think you're on a better track by going straight
> to the system tables.)
> Basically, your start is correct in that
> select *
> from sysobjects
> where parent_obj = object_id('<tablename>')
> will return all of a table's constraints.
> Since you want column information for each constraint, you're also going
to
> have problems when a constraint (PK, UQ, FK) covers more than one of a
> table's columns. You need to decide whether to have multiple column
> constraints come back as multiple rows or as a comma-delimited string
(like
> sp_helpconstraint or sp_helpindex do)
> Do you like the output of sp_helpconstraint? If so, I would recommend you
> just rewrite sp_helpconstraint, modifying it to take a table name and
owner
> as parameters, add the appropriate filter, and make it return only one
> result set and just the resulting columns you want. Be sure to test on
> multiple-column constraints.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> will
types[vbcol=seagreen]
> have
>
How to determine actual constraint name by passing a column name
I need a query in which I can pass in a column name and table name and will
be provided all constraints ( all types ) for that given column in that
table. I've created and found several queries that work for various types
but have not found one that does what I need for Unique constraints. I have
the following for FKs, but it doesn't work for UQs
selectdb_name()as DATABASE_name
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid)as OWNER
,c_obj.nameas CONSTRAINT_NAME
,col.nameas COLUMN_NAME
,col.colidas ORDINAL_POSITION
,c_obj.xtypeas XTYPE
from
sysobjectsc_obj
join sysobjectst_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id= con.constid
join syscolumnscol on t_obj.id = col.id and con.colid = col.colid
where
c_obj.xtype= 'F'
ORDER BY t_obj.name
If anyone can point out what I am missing, I'd really appreciate it.
Thanks
Rachel
Did you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN_USAGE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
>I need a query in which I can pass in a column name and table name and will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel
|||Ken,
Your query will not pick up unique and primary key constraints - they have
no data in syscolumns. To "fix" your query, make the join with syscolumns a
LEFT join:
> LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
Now, however, your 'Column' column will be null for PK and UQ constraints,
because they are implemented as indexes and their columns are stored in
sysindexes, not sysconstraints.
(BTW, the INFORMATION_SCHEMA views will not return any data regarding
default constraints, so I think you're on a better track by going straight
to the system tables.)
Basically, your start is correct in that
select *
from sysobjects
where parent_obj = object_id('<tablename>')
will return all of a table's constraints.
Since you want column information for each constraint, you're also going to
have problems when a constraint (PK, UQ, FK) covers more than one of a
table's columns. You need to decide whether to have multiple column
constraints come back as multiple rows or as a comma-delimited string (like
sp_helpconstraint or sp_helpindex do)
Do you like the output of sp_helpconstraint? If so, I would recommend you
just rewrite sp_helpconstraint, modifying it to take a table name and owner
as parameters, add the appropriate filter, and make it return only one
result set and just the resulting columns you want. Be sure to test on
multiple-column constraints.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> I need a query in which I can pass in a column name and table name and
will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I
have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel
|||Of course I meant rewrite sp_helpconstraint as a NEW stored procedure, with
a different name!
Ron
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23GUlV7U7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Ken,
> Your query will not pick up unique and primary key constraints - they have
> no data in syscolumns. To "fix" your query, make the join with syscolumns
a
> LEFT join:
> Now, however, your 'Column' column will be null for PK and UQ constraints,
> because they are implemented as indexes and their columns are stored in
> sysindexes, not sysconstraints.
> (BTW, the INFORMATION_SCHEMA views will not return any data regarding
> default constraints, so I think you're on a better track by going straight
> to the system tables.)
> Basically, your start is correct in that
> select *
> from sysobjects
> where parent_obj = object_id('<tablename>')
> will return all of a table's constraints.
> Since you want column information for each constraint, you're also going
to
> have problems when a constraint (PK, UQ, FK) covers more than one of a
> table's columns. You need to decide whether to have multiple column
> constraints come back as multiple rows or as a comma-delimited string
(like
> sp_helpconstraint or sp_helpindex do)
> Do you like the output of sp_helpconstraint? If so, I would recommend you
> just rewrite sp_helpconstraint, modifying it to take a table name and
owner[vbcol=seagreen]
> as parameters, add the appropriate filter, and make it return only one
> result set and just the resulting columns you want. Be sure to test on
> multiple-column constraints.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> will
types
> have
>
sql
be provided all constraints ( all types ) for that given column in that
table. I've created and found several queries that work for various types
but have not found one that does what I need for Unique constraints. I have
the following for FKs, but it doesn't work for UQs
selectdb_name()as DATABASE_name
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid)as OWNER
,c_obj.nameas CONSTRAINT_NAME
,col.nameas COLUMN_NAME
,col.colidas ORDINAL_POSITION
,c_obj.xtypeas XTYPE
from
sysobjectsc_obj
join sysobjectst_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id= con.constid
join syscolumnscol on t_obj.id = col.id and con.colid = col.colid
where
c_obj.xtype= 'F'
ORDER BY t_obj.name
If anyone can point out what I am missing, I'd really appreciate it.
Thanks
Rachel
Did you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN_USAGE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
>I need a query in which I can pass in a column name and table name and will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel
|||Ken,
Your query will not pick up unique and primary key constraints - they have
no data in syscolumns. To "fix" your query, make the join with syscolumns a
LEFT join:
> LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
Now, however, your 'Column' column will be null for PK and UQ constraints,
because they are implemented as indexes and their columns are stored in
sysindexes, not sysconstraints.
(BTW, the INFORMATION_SCHEMA views will not return any data regarding
default constraints, so I think you're on a better track by going straight
to the system tables.)
Basically, your start is correct in that
select *
from sysobjects
where parent_obj = object_id('<tablename>')
will return all of a table's constraints.
Since you want column information for each constraint, you're also going to
have problems when a constraint (PK, UQ, FK) covers more than one of a
table's columns. You need to decide whether to have multiple column
constraints come back as multiple rows or as a comma-delimited string (like
sp_helpconstraint or sp_helpindex do)
Do you like the output of sp_helpconstraint? If so, I would recommend you
just rewrite sp_helpconstraint, modifying it to take a table name and owner
as parameters, add the appropriate filter, and make it return only one
result set and just the resulting columns you want. Be sure to test on
multiple-column constraints.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> I need a query in which I can pass in a column name and table name and
will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I
have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel
|||Of course I meant rewrite sp_helpconstraint as a NEW stored procedure, with
a different name!
Ron
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23GUlV7U7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Ken,
> Your query will not pick up unique and primary key constraints - they have
> no data in syscolumns. To "fix" your query, make the join with syscolumns
a
> LEFT join:
> Now, however, your 'Column' column will be null for PK and UQ constraints,
> because they are implemented as indexes and their columns are stored in
> sysindexes, not sysconstraints.
> (BTW, the INFORMATION_SCHEMA views will not return any data regarding
> default constraints, so I think you're on a better track by going straight
> to the system tables.)
> Basically, your start is correct in that
> select *
> from sysobjects
> where parent_obj = object_id('<tablename>')
> will return all of a table's constraints.
> Since you want column information for each constraint, you're also going
to
> have problems when a constraint (PK, UQ, FK) covers more than one of a
> table's columns. You need to decide whether to have multiple column
> constraints come back as multiple rows or as a comma-delimited string
(like
> sp_helpconstraint or sp_helpindex do)
> Do you like the output of sp_helpconstraint? If so, I would recommend you
> just rewrite sp_helpconstraint, modifying it to take a table name and
owner[vbcol=seagreen]
> as parameters, add the appropriate filter, and make it return only one
> result set and just the resulting columns you want. Be sure to test on
> multiple-column constraints.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> will
types
> have
>
sql
Monday, March 19, 2012
How to detect at the subscriber that a replication is being (re)initialized.
Is there a method/query to discover that the subscriber's databasereplication is being reinitialized?
The problem is that the applications which is connects to this database crashes when the replication is (re)initializing.
Thanx, RalfYou would have to query the tables at the distribution database, this type of info is not stored at the subscriber. You could also try to reinitialize during offpeak hours.
How to detect at the subscriber that a replication is being (re)initialized.
Is there a method/query to discover that the subscriber's databasereplication is being reinitialized?
The problem is that the applications which is connects to this database crashes when the replication is (re)initializing.
Thanx, RalfYou would have to query the tables at the distribution database, this type of info is not stored at the subscriber. You could also try to reinitialize during offpeak hours.
how to design a thorough test plan?
I am going to handle a test to a list of querys to find their efficency(spending of time)
here is my test plan:
there are query ABC..., and insert all querys into a table called querytbl;
open a cursor for all records from querytbl;
fetch next query from cursor;
while @.@.fetchstatus = 0
begin
exec query for 3 times and calculate average spending of time;
fetch next query from cursor;
end
...
Is there any better test plan?(just test spending of time)
or test tools?One of the things I think you would want to include is the changing of parameter data (if applicable).
by that I mean that...
select * from tblMyTest where MyID = 12345
might return a lot faster then
select * from tblMyTest where MyID = 54321
depending on how the tables have been constructed.
You probably want to test with different levels of data as well eg, 10000 record, 1000000 records etc.
What exactly are your trying to prove by your testing? Performance obviously, but are you also stress testing, load testing and durability testing, all of which are performance related.
HTH.|||The main purpose of the test plan is to compare perfomance of the same querys to different databases which have same data but different Logical/phsical structure, or to compare performance of different versions of the same query to same database.
---may call it "test different structure's performance"?
we do that because we want to get a general contractive performance report of all querys or versions when we want make some change to databases or querys, that'll help us to decide whether to apply the change.|||Okie, well in that case one of the things you probably want to include in your testing is how the query performs when other activities are taking place on the database tables that the query is referencing.
You may find that despite the fact that 70% or the time the 3 seconds query is faster, 30% of the time the query take 10 seconds longer because of the locking that is involved in the query.|||thanks! Actually All querys is executed in sequence in a batch,and there is only one batch running,we will stop other clients also,so I think In that case wonnt occur a lock.
one thing I am not sure is that whether a query will run faster or later if the query was run in different order in sequence?|||I can't think of any reason why it would,... but you might want to try it just to make sure...|||thanks for advises!
here is my test plan:
there are query ABC..., and insert all querys into a table called querytbl;
open a cursor for all records from querytbl;
fetch next query from cursor;
while @.@.fetchstatus = 0
begin
exec query for 3 times and calculate average spending of time;
fetch next query from cursor;
end
...
Is there any better test plan?(just test spending of time)
or test tools?One of the things I think you would want to include is the changing of parameter data (if applicable).
by that I mean that...
select * from tblMyTest where MyID = 12345
might return a lot faster then
select * from tblMyTest where MyID = 54321
depending on how the tables have been constructed.
You probably want to test with different levels of data as well eg, 10000 record, 1000000 records etc.
What exactly are your trying to prove by your testing? Performance obviously, but are you also stress testing, load testing and durability testing, all of which are performance related.
HTH.|||The main purpose of the test plan is to compare perfomance of the same querys to different databases which have same data but different Logical/phsical structure, or to compare performance of different versions of the same query to same database.
---may call it "test different structure's performance"?
we do that because we want to get a general contractive performance report of all querys or versions when we want make some change to databases or querys, that'll help us to decide whether to apply the change.|||Okie, well in that case one of the things you probably want to include in your testing is how the query performs when other activities are taking place on the database tables that the query is referencing.
You may find that despite the fact that 70% or the time the 3 seconds query is faster, 30% of the time the query take 10 seconds longer because of the locking that is involved in the query.|||thanks! Actually All querys is executed in sequence in a batch,and there is only one batch running,we will stop other clients also,so I think In that case wonnt occur a lock.
one thing I am not sure is that whether a query will run faster or later if the query was run in different order in sequence?|||I can't think of any reason why it would,... but you might want to try it just to make sure...|||thanks for advises!
Wednesday, March 7, 2012
How to delete from two tables at once
I have 2 tables "Orders" and "OrderProducts"
In my application, there are moments when I clean up these tables.
There is a query that looks for some flag in the "Orders" table, and deletes the records.
But there are related (PK-FK) records in the "OrderProducts" table.
How can I delete also these records in the same query?
I suppose you use SQL 2005 and Sql Server management Studio. If so, then you must specify INSERT and UPDATE specification during the PK-FK constraint creation, and set the delete rule as 'cascade'. If you do so, then if you delete an 'order', then all the corresponding 'orderProducts' will also be deleted.
|||Either delete all the records from the child table first or use the "ON DELETE CASCADE" method.
Friday, February 24, 2012
how to delete a job
How to delete a job by using SQL Query analyzer?Have a look in these tables in your msdb database:
sysjobs,
sysdbmaintplan_jobs,
sysjobschedules,
sysjobhistory,
sysjobservers,
systaskids,
sysjobsteps
Keep in mind that I haven't tested this solution. Is there no way you can delete the job through enterprise manager?|||Originally posted by bmalar
Have a look in these tables in your msdb database:
sysjobs,
sysdbmaintplan_jobs,
sysjobschedules,
sysjobhistory,
sysjobservers,
systaskids,
sysjobsteps
Keep in mind that I haven't tested this solution. Is there no way you can delete the job through enterprise manager?
use SP_DELETE_JOB
for details search in BOL.|||Originally posted by harshal_in
use SP_DELETE_JOB
for details search in BOL.
A much better solution.
sysjobs,
sysdbmaintplan_jobs,
sysjobschedules,
sysjobhistory,
sysjobservers,
systaskids,
sysjobsteps
Keep in mind that I haven't tested this solution. Is there no way you can delete the job through enterprise manager?|||Originally posted by bmalar
Have a look in these tables in your msdb database:
sysjobs,
sysdbmaintplan_jobs,
sysjobschedules,
sysjobhistory,
sysjobservers,
systaskids,
sysjobsteps
Keep in mind that I haven't tested this solution. Is there no way you can delete the job through enterprise manager?
use SP_DELETE_JOB
for details search in BOL.|||Originally posted by harshal_in
use SP_DELETE_JOB
for details search in BOL.
A much better solution.
How to delete a counter in a field ?
I want to delete a counter using an SQL query (alter, drop...)
What could be the SQL queryI want to delete a counter using an SQL query (alter, drop...)
What could be the SQL query
Are your trying to reset an identity column or to drop it?
Plz specify that clearly...
To reset an identity column use
DBCC CHECKIDENT ('table_name',RESEED,0)|||I want to delete the counter (identity) but keep the column.|||Unfortunately, you can't reset the IDENTITY property in Microsoft SQL because of the way that it is implemented. The only safe way to do this is:ALTER TABLE myTable
ADD COLUMN new_column INT NOT NULL -- set type and NULL-ability to taste
GO
UPDATE myTable
SET new_column = old_column
GO
ALTER TABLE myTable
DROP COLUMN old_column
GO
EXECUTE sp_rename 'mytable.new_colum', 'mytable.old_column'
GO-PatP
What could be the SQL queryI want to delete a counter using an SQL query (alter, drop...)
What could be the SQL query
Are your trying to reset an identity column or to drop it?
Plz specify that clearly...
To reset an identity column use
DBCC CHECKIDENT ('table_name',RESEED,0)|||I want to delete the counter (identity) but keep the column.|||Unfortunately, you can't reset the IDENTITY property in Microsoft SQL because of the way that it is implemented. The only safe way to do this is:ALTER TABLE myTable
ADD COLUMN new_column INT NOT NULL -- set type and NULL-ability to taste
GO
UPDATE myTable
SET new_column = old_column
GO
ALTER TABLE myTable
DROP COLUMN old_column
GO
EXECUTE sp_rename 'mytable.new_colum', 'mytable.old_column'
GO-PatP
how to delete a column using script code
hi all
iam having a query , c guys iam having a table in sql with some coulmns in it , i have a column named as country in the table , now wat i want to be done is , i want to delete the column country based on some conditions , i ve to write a script code as : i ve to check if the column is there already or not if its there it shld delete the column or if its not there it shld not show any error and just return empty handed thats all , i dont know wat to be done , so if anyone knows abt it pls do send as soon as possible guys , hopefully waiting for a reply
Note : any dbts pls do mail me again
Venkatesan
I understand this is your first post. Welcome to the forums.
Some info that might help you:
If you want a proper response to your question, you need to explain the facts, provide the table structure(s), some sample data. You dont need to provide your entire HTML code from visual studio. Only relevant code. Any amount of ambiguity or missing information can lead people trying to answer your question away from your post, to a more informative post. Otherwise your post will go down the pages unanswered.
Now, coming back to your question, do you want to delete the column or the values in the column? Posting some sample condition would also help!!
|||hi dinakar
thanks for ur reply , first let me apologise , c yar i dont have any code to post it here , actually i dont know how to write one , i will explain my req again , 1st i have a table named as tbl_employees which has some columns and in that there is a column named as country , intially for live database that country column will not be there and if clients( i mean in live ) wants to have it , then they can add it , the scenario is like this , so as a developer we used to do it and ensure it that its working in local , after it works we have to put it in live , so that time we cannot delete it manually , for that we have to send a script file to check whether the column is there or not , if its there i have to delete the country column through a query thats wat i dont know how to do , so wat i need is to code to delete the country column itself not the values in it , i think i have explained to u well , waiting for a reply asap, thanks in advance
Venkatesan
ALTER TABLE yourTableDROP COLUMNCountry
|||thanks manSunday, February 19, 2012
How to define the column size in the query
I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?
On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows?
|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?
On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows?
|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
How to define the column size in the query
I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
How to define the column size in the query
I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?
Subscribe to:
Posts (Atom)