display SQL Server: identify

Showing posts with label identify. Show all posts

Friday, March 23, 2012

How do I identify whether a column is set up to automatically increment

How do I programmatically identify whether a column is set up to automatically increment? I am looking for a field in the syscolumns table which identifies whether a referenced column is not updatable because it set up to auto increment. Thanks.Check out syscolumns in bol. The value is stored in status as hex 0x80 or 128 (decimal) as a bit flag. Also, you might be able to use the colstat field in syscolumns (1 = identity) but I have not found supported documentation - which means it may be that today but not tomorrow.|||Q1 How do I programmatically identify whether a column is set up to automatically increment?
I am looking for a field in the syscolumns table which identifies whether a referenced column is not updatable because it set up to auto increment. Thanks.

A1 Use sp_columns (check the Type_Name result set column). For example:

exec sp_columns
@.table_name = 'YourTable',
@.column_name = 'MysteryColumn'

You could also use the third result set of sp_help (or sp_columns more generally):

USE pubs
EXEC sp_help jobs
EXEC sp_columns jobs
EXEC sp_columns @.table_name = 'jobs', @.column_name = 'job_id'

-- compare to (no identity column)
EXEC sp_help authors
EXEC sp_columns authors|||The sp_columns/sp_help just uses the syscolumns database - so if you are looking for a specific answer use the bit flag from syscolumns. Using sp_columns/sp_help adds another layer of complexity that you can pull directly from syscolumns. As a matter of fact, sp_help uses the colstat column to determine an identity column - again this is undocumentated but used with sp_help.

You can use the following as a template:

select a.name, b.name from syscolumns as a inner join sysobjects as b on a.id = b.id where a.status & 0x80 > 0

It will return the column name and object name that has an identity field. You will need to fine tune this for you scenario - but you will be able to return a count or other specific information directly.

Good luck.|||RE:
The sp_columns/sp_help just uses the syscolumns database - so if you are looking for a specific answer use the bit flag from syscolumns. Using sp_columns/sp_help adds another layer of complexity that you can pull directly from syscolumns. As a matter of fact, sp_help uses the colstat column to determine an identity column - again this is undocumentated but used with sp_help.

Selecting directly from system tables, (and not isolating user stored procedurees / applications from changes to underlying system tables in any way) may add multiple layers of "complexity" (in the form of maintenance checks and tasks to perform with every Sql Server service pack), as well. Such practices have also resulted in worse, (in the form of addressing / correcting corrupt data, and troubleshooting stored procedurees / applications that "mysteriously" began to malfunction and generate corrupt data and / or corrupt existing data), following the application of Sql Server upgrades that alter system tables.

Adding a layer of abstraction is exactly the point. Doing so in an organized manner often provides significant benefits in regard to minimizing support and maintenance resource use, and costs (especially in relation to addressing and correcting corrupt data, which may cause a business irreparable damage). If selecting directly from system tables is unavoidable (or using sp_columns/sp_help adds "too much" complexity); consider centralizing maintenance issues by providing your own private level of abstraction e.g.(sp_TableIdentityColumns).

Specifically: Consider creating your own user special stored procedures / functions (that select directly from system tables) and calling them in any other user stored procedures and applications. That way, (when Sql Server upgrades, service pack, or hot fix changes alter the underlying system tables radically), you need only change a few user special stored procedures / functions (rather than every procedure / application that calls / uses them).|||Its always recommended to lookup at INFORMATION SCHEMA VIEWS and not to query against SYSTEM tables. REfer to Books online for more information .

HTH
Originally posted by RickLambert
How do I programmatically identify whether a column is set up to automatically increment? I am looking for a field in the syscolumns table which identifies whether a referenced column is not updatable because it set up to auto increment. Thanks.|||RE: Its always recommended to lookup at INFORMATION SCHEMA VIEWS and not to query against SYSTEM tables. REfer to Books online for more information .
HTH

That would normally have been one reccomendation / suggestion; but, I do not know of any Information_Schema View (COLUMNS, TABLES, and TABLE_CONSTRAINTS, etc.) that provides identity column information?

Could you please share where identity column information is available in the Information_Schema views?? (If it is there, I would appreciate knowing where it may be found. - Thanks.)

Information_Schema views:

CHECK_CONSTRAINTS
COLUMNS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAINS
DOMAIN_CONSTRAINTS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINES
ROUTINE_COLUMNS
SCHEMATA
TABLES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
VIEWS
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE|||Thank you everyone for your response. This web site is a most impressive resource! So, I think I will use the ColStat=1, since this appears to be the most straightforward approach and my paranoia level is not very high. My objective is to create a view which contains a list of all columns and their characteristics, for the purpose of programmatically building insert, update, and delete stored procedures. Thanks again!|||I am curious if you are accessing this data exclusively in sql server or are you going to have an application access this data - say through visual basic or c++. Also, is it possible that you would like to have this functionality accessible to all databases or will it be isolated to one ?|||Hi rnealejr:

I am using an MS Access Data Project with SQL Server data to build SQL Server-specific stored procedures. However stored procedure naming and the parameters passed will remain constant regardless of the underlying database.

So I could use a similar approach to create Oracle stored procedures which would be referenced identically in code; just the connect string would change.

Similarly, the process of building the stored procedures is not hard-coded. A table contains the db-specific syntax for each type of stored procedure (insert-update-delete). Then this is is used by the sp-building routine which uses ADO to cycle through each of the rows in the view containing all the column characteristics of every table of the current database.

By the way, is there an easy way to determine the unique identifier of each table?

-RAL|||Since you are using ADO you could use the information from the provider and determine whether a column is an identity column (know that with ADO the provider has a wealth of information that may not be obvious) - but that may not be appropriate in this case. How do you compile these dynamic stored procedures ? Or is it just the syntax you are dynamically creating ? Can you give an example of the process ?

My other suggestion is to create an Information_Schema view - this would allow you to store the view in one location but run it in the context of the any (current) database.

Are you referring to the uniqueidentifier data type ?|||You can use the Information_Schema.columns view. Look under the DATA_TYPE column.|||Originally posted by rnealejr
Since you are using ADO you could use the information from the provider and determine whether a column is an identity column (know that with ADO the provider has a wealth of information that may not be obvious) - but that may not be appropriate in this case. How do you compile these dynamic stored procedures ? Or is it just the syntax you are dynamically creating ? Can you give an example of the process ?

My other suggestion is to create an Information_Schema view - this would allow you to store the view in one location but run it in the context of the any (current) database.

Are you referring to the uniqueidentifier data type ?

By unique identifier I meant primary key. I think this can be extracted using a view joining sysObjects-sysIndexes-sysIndexKeys.

Not sure how ADO would know how to build these stored procedures without reference to an appropriate view.

Here is an example of the syntax stored in the table referenced by the ADO code:

Create Procedure s_Insert_<<TableName>>
(
<<ParameterList>>
)

As
set nocount on

Insert Into dbo.<<TableName>> (
<<FieldList>>

) Values (

<<ValueList>>
)
return|||Originally posted by rnealejr
You can use the Information_Schema.columns view. Look under the DATA_TYPE column.

I am not familiar with how to access this programmatically.|||RE:
By unique identifier I meant primary key. I think this can be extracted using a view joining sysObjects-sysIndexes-sysIndexKeys.
...
Information_Schema.columns view. Look under the DATA_TYPE column.
I am not familiar with how to access this programmatically.

Q1 [How may one identify ALL unique (and candidate keys, including compound keys) columns in a table?]
A1 MS Sql Server 2k and earlier implement unique columns at the DBMS level via indices. (Looking at a table object's indices is therefore a good way to find columns that are implemented as such using built in DBMS methods. However privately maintained unique columns that do not use built in DBMS functionality to guarantee unique row values may not necessarily be identifiable using this approach.)

An example (to identify ALL unique columns (candidate keys, including compound keys):
Use Northwind
Go
exec sp_HelpIndex @.objname = 'Orders'
---

Q2 [RE: The Information_Schema.columns view; I am not familiar with how to access this programmatically?]
A2 For an example, run:

Use Northwind
Go
Select TABLE_NAME, COLUMN_NAME, DATA_TYPE
From [Information_Schema].[columns]
Where
[TABLE_NAME] = 'Orders'|||Thanx, DBA!|||Thanx, DBA!
You are welcome; hopefully some of it will help you create more robust apps.|||In a similar question, how do I find out if there are any table(s) that using IDENTITY column or numerical column as an IDENTITY, and using it to figure out if the column will reach the Max. value (like the SSN) very soon ?

Thanks|||You can use the following:

DBCC CHECKIDENT ('table_name', NORESEED)

What do you mean by max value - 2,147,483,647 ? Which data type are you using for your identity int or bigint (ss2k only) ?|||or use IDENT_CURRENT('table_name')|||Thanks rnealejr. I am aware of the DBCC CHECKIDENT and IDENT_CURRENT function, but what if the column is NOT employed the IDENTITY but other numeric data type (int or bigint) ? For example, the PurchaseOrder column is using INT as data type and it started at seed 2,000,000,000 (2 billion), and increment by 1000 ... I wanted to find out if such column is exists and how soon it will reach the Max value of INT.

Thanks|||So you have other columns that are not identity columns but numeric columns and need to check and see if you are near the cap for that data type - is this an accurate picture ?|||You got that 100% corrected. :-)

Thanks rnealejr|||So you have 2 options:

1. Search through every table for int/bigints and compare against max value.
2. Create a table of only the columns you need to check.

So which one do you want to do ?|||I can find the IDENT column with this script:

select b.name 'Table Name', a.name 'Column Name'
from syscolumns as a inner join sysobjects as b on a.id = b.id
where b.type = 'u'
and a.status & 0x80 > 0

and I needed to resolve the option#1 you described.

Regards with kindly,

Dam234|||It will not matter whether a column is ident or not - you will be searching for all int/bigint which will include identity columns as well.

Let me see what I can scratch up.|||Portions of this may (or may not) exactly address the issue(s).

However, perhaps some of the following may be helpful (if a bit
repetitive):

1) An Identity column in MS Sql Server 2k may be defined using
any of the following types: (filling the range for a numeric / decimal beginning from - 10^38 +1 to 10^38 - 1 would take a while given 1++ )

[bigint]
[int]
[tinyint]
[numeric]
[decimal]

2) For columns that do use built in DBMS functionality a recommendation for finding out information about candidate
keys, including compound keys is to use exec sp_HelpIndex
@.objname = 'TargetTableName'; as MS Sql Server 2k and earlier
implement unique columns at the DBMS level via indices. Looking
at a table object's indices is a means of identifying columns
that are implemented as such using built in DBMS methods.
(Privately maintained unique columns that do not use built in
DBMS functionality to guarantee unique row values may not
necessarily be identifiable using this approach.)

3) For columns that do not use built in DBMS functionality to
guarantee unique row values writing custom functions / stored
procedures may be necessary. This would include columns that are
not DBMS supported Identity columns per se, that are instead
maintained by user created "identity type" functionality. To
determine if such columns are near the limit for the "custom
data type" would obviously depend not only on the current value
and absolute limit of the underlying "type", but also the
private algorithm itself. (A private algorithm may increment,
decrement etc., by different intervals and may or may not
recycle previously used / deleted values and may or may not be
limited to decimal representations, e.g. hex or greater bases could be used.).

4) An example (to identify candidate keys, including compound
keys that DO use built in DBMS methods):
Use Northwind
Go
exec sp_HelpIndex @.objname = 'Orders'

5) A recommended general means for finding out information about DBMS Identity columns is to use sp_columns, and / or sp_help, and /
or as rnealejr has noted, DBCC CHECKIDENT ('table_name', NORESEED)
and / or IDENT_CURRENT('table_name').

For example:

USE pubs
EXEC sp_help jobs
EXEC sp_columns jobs
EXEC sp_columns @.table_name = 'jobs', @.column_name = 'job_id'
Select IDENT_CURRENT('jobs') As 'IdentCurrent'
Go
DBCC CHECKIDENT ('jobs', NORESEED)

6) More specific means for finding out information about DBMS Identity
columns:

If the additional overhead involved (in the form of maintenance)
is acceptable, one may create user special stored procedures /
functions to provide a variety of additional information about
table objects with identity columns. (Similar to
sp_TableIdentityColumnMetaData and
fn_TableIdentityColumnsMetaData posted here, or
sp_TableIdentityColumns and fn_TableIdentityColumns, posted
earlier in this thread.)

Note: When Sql Server upgrades, service pack, or hot fix changes
alter the underlying system tables involved such user special
stored procedures / functions (that select directly from system
tables) may very well require modification to continue to run
and / or return correct result sets.|||Thanks DBA. I already know how to identify IDENT column, the other
numerical columns are the one that I have to deal with.sql

How do I identify the replication generated column(s) in a table

I need to dynamically generate a select command that does not include
replication generated columns. Is there a way to do this? I could use
GetOleDbSchemaTable and filter for column names with "rowguid" but that does
not seem robust to me. Is there a system table or stored procedure that can
help? Dropping a replication subscription and publication removes these
columns so I suspect that the information is available somewhere.
Thanks
Dropping a publication doesn't drop the guid columns added to a merge
publication. As far as I know we can't identify those columns added.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||What about something like this:
declare @.mystring varchar(2000)
set @.mystring='select '
select @.mystring=@.mystring+' '+name+', ' From syscolumns where
id=object_id('customers')
and name <>'rowguid'
select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from customers'
print @.mystring
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kohn" <Kohn@.discussions.microsoft.com> wrote in message
news:915755D4-E2FC-4790-B0E8-6E493DE1BA4F@.microsoft.com...
>I need to dynamically generate a select command that does not include
> replication generated columns. Is there a way to do this? I could use
> GetOleDbSchemaTable and filter for column names with "rowguid" but that
> does
> not seem robust to me. Is there a system table or stored procedure that
> can
> help? Dropping a replication subscription and publication removes these
> columns so I suspect that the information is available somewhere.
> Thanks
>
|||Hi Hilary - unfortunately this doesn't work

. We could query for the
rowguid column on the table, but that might already have existed prior to
the replication setup. As far as I can tell there isn't a way of knowing if
the rowguid column is added by the replication setup or by a user
beforehand.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||It will work for the majority of the cases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O$uP0uJNHHA.3424@.TK2MSFTNGP02.phx.gbl...
> Hi Hilary - unfortunately this doesn't work

. We could query for the
> rowguid column on the table, but that might already have existed prior to
> the replication setup. As far as I can tell there isn't a way of knowing
> if the rowguid column is added by the replication setup or by a user
> beforehand.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Another thing to take into account is that replication does not require
the column to be called rowguid. It just requires a column with the
ROWGUIDCOL property set. Maybe that is where you should start looking.
Hilary Cotter wrote:
> What about something like this:
> declare @.mystring varchar(2000)
> set @.mystring='select '
> select @.mystring=@.mystring+' '+name+', ' From syscolumns where
> id=object_id('customers')
> and name <>'rowguid'
> select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from customers'
> print @.mystring
>
|||I had a quick look and it seems you have to look for colomns where
syscolumns.colstat = 2. You can also only have one column per table with
the ROWGUIDCOL property set, so you can be pretty sure that is the
column used by replication. So to modify Hilary's query:
declare @.mystring varchar(2000)
set @.mystring='select '
select @.mystring=@.mystring+' '+name+', ' From syscolumns where
id=object_id('customers')
and colstat <> 2
select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from
customers'
print @.mystring
JE wrote:[vbcol=seagreen]
> Another thing to take into account is that replication does not require
> the column to be called rowguid. It just requires a column with the
> ROWGUIDCOL property set. Maybe that is where you should start looking.
>
> Hilary Cotter wrote:
|||The information for identifying the column with the rowguid property solves
the problem. My app retrieves the information with the GetOleDbSchemaTable
function (see below).
By the way, rowguid columns created by the wizard are removed when dropping
the publication. I suspect it uses the preserve_rowguidcol column in the
sysmergearticles tables.
cn.Open()
Dim t As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
New Object() {Nothing, Nothing, TableName, Nothing})
cn.Close()
cn.Dispose()
Dim SelectRows() As DataRow
SelectRows = t.Select("(DATA_TYPE <> 72) AND
(COLUMN_HASDEFAULT=FALSE) AND (COLUMN_HASDEFAULT=False) AND
((ISNULL(COLUMN_DEFAULT,'T')='T') OR
(COLUMN_DEFAULT<>'(newsequentialid())'))")
Dim SelectListStringBuilder As New System.Text.StringBuilder
For Each r As DataRow In SelectRows
SelectListStringBuilder.Append(r.Item("COLUMN_NAME "))
SelectListStringBuilder.Append(",")
Next
SelectListStringBuilder.Length -= 1
Debug.WriteLine(SelectListStringBuilder.ToString)
Thanks for the help

How do I identify dependencies before adding a column to a table?

I need to add a new column to a frequently used table in Production.
I want to make sure that the addition of the column does not break any
existing code in stored procs or triggers.
I can run sp_depends to identify potential problems but I believe this
system procedure is not entirely reliable.
Is the best approach a text search of sysobjects/syscomments to identify
dependencies? And other than "SELECT *," what are some things that might
break that I should be looking for?
Thanks
Dave> And other than "SELECT *," what are some things that might
> break that I should be looking for?
Off the top of my head, there is at least one other thing. The following
will now fail with incorrect number of columns specified:
INSERT table VALUES(blah, blah, blah)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||In trigger code, the function columns_updated() is positional.
"DaveF" <davef@.comcast.net> wrote in message
news:uNHgPrzwDHA.2784@.tk2msftngp13.phx.gbl...
> I need to add a new column to a frequently used table in Production.
> I want to make sure that the addition of the column does not break any
> existing code in stored procs or triggers.
> I can run sp_depends to identify potential problems but I believe this
> system procedure is not entirely reliable.
> Is the best approach a text search of sysobjects/syscomments to identify
> dependencies? And other than "SELECT *," what are some things that might
> break that I should be looking for?
> Thanks
> Dave
>|||Sp_depends only contains info about objects that existed when you created
the sp... So it is possible that sp_depends does NOT show all sp
dependencies... I generally script out all sps and do a find...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"DaveF" <davef@.comcast.net> wrote in message
news:uNHgPrzwDHA.2784@.tk2msftngp13.phx.gbl...
> I need to add a new column to a frequently used table in Production.
> I want to make sure that the addition of the column does not break any
> existing code in stored procs or triggers.
> I can run sp_depends to identify potential problems but I believe this
> system procedure is not entirely reliable.
> Is the best approach a text search of sysobjects/syscomments to identify
> dependencies? And other than "SELECT *," what are some things that might
> break that I should be looking for?
> Thanks
> Dave
>|||> In trigger code, the function columns_updated() is positional.
True. Though for the benefit of the original poster, code using
columns_updated() would only be affected if you used Enterprise Manager to
inject the column in the middle of the table. If you use ALTER TABLE ...
ADD COLUMN, the ordinal_position is next, so that existing code for
columns_updated should not have any effect...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks to everyone who responded.
I created a stored proc to search the code for potential problems.
This approach helped me find some things that sp_depends did not.
For anyone else who may face this problem, I include the proc below.
Thanks again.
Dave
USE admin
GO
--sample call:
--EXEC checkdepends 'mls', 'select *'
--dbo.checkdepends.PRC
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[checkdepends]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[checkdepends]
GO
CREATE PROC checkdepends
@.objname varchar(128) --object on which to identify dependencies (e.g.,
table name)
,@.str varchar(60) -- string used to search for dependencies (e.g., SELECT
* )
AS
DECLARE @.objtype varchar(24) --type of object (stored proc, trigger)
,@.c int -- record counter for coursor
,@.i int -- affected records counter
SET @.c = 0
SET @.i = 0
SET @.objname = '%' + @.objname + '%'
SET @.str = '%' + @.str + '%'
SET NOCOUNT ON
--build a table to hold the list of referencing objects (i.e., any db
object that references the target object #objname)
IF OBJECT_ID('tempdb..#refs','u') IS NOT NULL
DROP TABLE #refs
CREATE TABLE #refs
(
objname varchar(128)
,objtype varchar(24)
)
--grab all of the objects that reference the target object in their code
INSERT INTO #refs
SELECT name, xtype FROM sysobjects o JOIN syscomments c ON c.id=o.id WHERE
text LIKE @.objname ORDER BY xtype
SELECT @.i= count(*)
FROM #refs
PRINT 'There are ' + str(@.i) + ' objects referencing ' + @.objname
PRINT ''
--create a table to hold the referencing object's code
IF OBJECT_ID('tempdb..#text','u') IS NOT NULL
DROP TABLE #text
CREATE TABLE #text (rowid int identity, obj_text varchar(8000))
--create a cursor to examine each referencing object in detail
DECLARE curs CURSOR
FOR
SELECT objname, objtype
FROM #refs
FOR READ ONLY
OPEN curs
FETCH NEXT FROM curs
INTO @.objname, @.objtype
WHILE @.@.fetch_status = 0
BEGIN
-- Add the proc code to the table (1 line of code = 1 record)
INSERT #text
EXEC sp_helptext @.objname
--check for the presence of search string within the referencing object's
code
SELECT @.i= count(*)
FROM #text
WHERE obj_text LIKE @.str
IF @.i > 0
BEGIN
PRINT
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++'
PRINT str(@.c) + '. ' + @.objname + '. Type = ' + @.objtype
PRINT ''
-- Select from the lines of code that have the string we are looking for
SELECT *
FROM #text
WHERE obj_text LIKE @.str
--empty the table to get ready for another proc
TRUNCATE TABLE #text
END
--increment the counter
SET @.c=@.c+1
FETCH NEXT FROM curs
INTO @.objname, @.objtype
END
CLOSE curs
DEALLOCATE curs|||Note that their still might be another problem, and that is if you have a
stored procedure > 8kb, a column or table name might actually start at
character 7995 and end at character 8012, which means that the result could
be split across separate columns in syscomments.
Might be safer to script the procedures, functions etc. and do a brute force
file-based search instead of using syscomments.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"DaveF" <davef@.comcast.net> wrote in message
news:uG1MTR1wDHA.1272@.TK2MSFTNGP12.phx.gbl...
> Thanks to everyone who responded.
> I created a stored proc to search the code for potential problems.
> This approach helped me find some things that sp_depends did not.
> For anyone else who may face this problem, I include the proc below.
> Thanks again.
> Dave
>
> USE admin
> GO
> --sample call:
> --EXEC checkdepends 'mls', 'select *'
> --dbo.checkdepends.PRC
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id => object_id(N'[dbo].[checkdepends]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [dbo].[checkdepends]
> GO
> CREATE PROC checkdepends
> @.objname varchar(128) --object on which to identify dependencies (e.g.,
> table name)
> ,@.str varchar(60) -- string used to search for dependencies (e.g.,
SELECT
> * )
> AS
> DECLARE @.objtype varchar(24) --type of object (stored proc, trigger)
> ,@.c int -- record counter for coursor
> ,@.i int -- affected records counter
> SET @.c = 0
> SET @.i = 0
> SET @.objname = '%' + @.objname + '%'
> SET @.str = '%' + @.str + '%'
> SET NOCOUNT ON
> --build a table to hold the list of referencing objects (i.e., any db
> object that references the target object #objname)
> IF OBJECT_ID('tempdb..#refs','u') IS NOT NULL
> DROP TABLE #refs
> CREATE TABLE #refs
> (
> objname varchar(128)
> ,objtype varchar(24)
> )
>
> --grab all of the objects that reference the target object in their code
> INSERT INTO #refs
> SELECT name, xtype FROM sysobjects o JOIN syscomments c ON c.id=o.id
WHERE
> text LIKE @.objname ORDER BY xtype
> SELECT @.i= count(*)
> FROM #refs
> PRINT 'There are ' + str(@.i) + ' objects referencing ' + @.objname
> PRINT ''
>
> --create a table to hold the referencing object's code
> IF OBJECT_ID('tempdb..#text','u') IS NOT NULL
> DROP TABLE #text
> CREATE TABLE #text (rowid int identity, obj_text varchar(8000))
>
> --create a cursor to examine each referencing object in detail
> DECLARE curs CURSOR
> FOR
> SELECT objname, objtype
> FROM #refs
> FOR READ ONLY
> OPEN curs
>
> FETCH NEXT FROM curs
> INTO @.objname, @.objtype
> WHILE @.@.fetch_status = 0
> BEGIN
> -- Add the proc code to the table (1 line of code = 1 record)
> INSERT #text
> EXEC sp_helptext @.objname
> --check for the presence of search string within the referencing
object's
> code
> SELECT @.i= count(*)
> FROM #text
> WHERE obj_text LIKE @.str
> IF @.i > 0
> BEGIN
> PRINT
>
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> +++++++++++'
> PRINT str(@.c) + '. ' + @.objname + '. Type = ' + @.objtype
> PRINT ''
> -- Select from the lines of code that have the string we are looking
for
> SELECT *
> FROM #text
> WHERE obj_text LIKE @.str
> --empty the table to get ready for another proc
> TRUNCATE TABLE #text
> END
> --increment the counter
> SET @.c=@.c+1
> FETCH NEXT FROM curs
> INTO @.objname, @.objtype
> END
>
> CLOSE curs
> DEALLOCATE curs
>
>
>|||> be split across separate columns in syscomments.
Err, separate rows/tuples, sorry.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||hello Dave,
I think the answer to your problem is simple. Build it. If
you do a complete build of all your source any errors that
your change could cause will be raised. Sounds easy when I
say it like this, however if you check out www.dbghost.com
it show how this can be made very simple.
All other code (exe,dll,asp) is commonly built to find
errors due to dependancy. Database code isn't (generally).
We would like to change this and take the guess work out
of making changes to your database code.
regards,
Mark Baekdal
www.dbghost.com
>--Original Message--
>I need to add a new column to a frequently used table in
Production.
>I want to make sure that the addition of the column does
not break any
>existing code in stored procs or triggers.
>I can run sp_depends to identify potential problems but I
believe this
>system procedure is not entirely reliable.
>Is the best approach a text search of
sysobjects/syscomments to identify
>dependencies? And other than "SELECT *," what are some
things that might
>break that I should be looking for?
>Thanks
>Dave
>
>.
>

How do I identify a command in a job step?

Hi
I'm having a job that runs a number of steps. One of them runs the CmdExec
"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs, but
how do I find out which one it is? I've looked a bit around to see if I can
find a table where I can find the number and then maybe see the name of the
job, but with no luck.
Can any of you help with this?
Regards
SteenWhen the DTSRun command uses /~Z, it means the command to
run the DTS package is encrypted. Run a trace or Profiler
when the job runs. This will allow you to get the package
name or package guid.
-Sue
On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
<SPE@.REMOVEdatea.dk> wrote:
>Hi
>I'm having a job that runs a number of steps. One of them runs the CmdExec
>"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs, but
>how do I find out which one it is? I've looked a bit around to see if I can
>find a table where I can find the number and then maybe see the name of the
>job, but with no luck.
>Can any of you help with this?
>
>Regards
>Steen
>|||Ok...found some more info about it in BOL (...just have to look for the
right thing..:-)..). The /Z argument apparently tells that the command line
is encrypted. That's fine, but...I still need to find out which command it's
running. Any change of doing that?
Steen
"Steen Persson" <SPE@.REMOVEdatea.dk> skrev i en meddelelse
news:O5Z2ZETVEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm having a job that runs a number of steps. One of them runs the CmdExec
> "DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
but
> how do I find out which one it is? I've looked a bit around to see if I
can
> find a table where I can find the number and then maybe see the name of
the
> job, but with no luck.
> Can any of you help with this?
>
> Regards
> Steen
>|||Thanks Sue
I've just tried to run a trace, but I must admit that my knowledge in
running traces might not be good enough, since I can't find any references
in there that indicates which package is being run.
Are there any easy or "obvious" data to look for to find out which package
is being executed?
Regards
Steen
"Sue Hoegemeier" <Sue_H@.nomail.please> skrev i en meddelelse
news:v4p5d05mm1ren0kqpp6ae5das1c3hknlbg@.4ax.com...
> When the DTSRun command uses /~Z, it means the command to
> run the DTS package is encrypted. Run a trace or Profiler
> when the job runs. This will allow you to get the package
> name or package guid.
> -Sue
> On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
> <SPE@.REMOVEdatea.dk> wrote:
> >Hi
> >
> >I'm having a job that runs a number of steps. One of them runs the
CmdExec
> >"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
but
> >how do I find out which one it is? I've looked a bit around to see if I
can
> >find a table where I can find the number and then maybe see the name of
the
> >job, but with no luck.
> >
> >Can any of you help with this?
> >
> >
> >Regards
> >Steen
> >
>|||Steen,
Capturing SQL:StmtCompleted or SP:Completed will give you
TextData values along the lines of:
exec msdb..sp_get_dtspackage N'YourPackage', null, null
-Sue
On Fri, 18 Jun 2004 15:26:26 +0200, "Steen Persson"
<SPE@.REMOVEdatea.dk> wrote:
>Thanks Sue
>I've just tried to run a trace, but I must admit that my knowledge in
>running traces might not be good enough, since I can't find any references
>in there that indicates which package is being run.
>Are there any easy or "obvious" data to look for to find out which package
>is being executed?
>Regards
>Steen
>"Sue Hoegemeier" <Sue_H@.nomail.please> skrev i en meddelelse
>news:v4p5d05mm1ren0kqpp6ae5das1c3hknlbg@.4ax.com...
>> When the DTSRun command uses /~Z, it means the command to
>> run the DTS package is encrypted. Run a trace or Profiler
>> when the job runs. This will allow you to get the package
>> name or package guid.
>> -Sue
>> On Fri, 18 Jun 2004 14:38:02 +0200, "Steen Persson"
>> <SPE@.REMOVEdatea.dk> wrote:
>> >Hi
>> >
>> >I'm having a job that runs a number of steps. One of them runs the
>CmdExec
>> >"DTSRun /~Z0x...." which fails. I assume it's a Local Package it runs,
>but
>> >how do I find out which one it is? I've looked a bit around to see if I
>can
>> >find a table where I can find the number and then maybe see the name of
>the
>> >job, but with no luck.
>> >
>> >Can any of you help with this?
>> >
>> >
>> >Regards
>> >Steen
>> >
>

Wednesday, March 21, 2012

How do I get to identify all tables accessed?

How do I get to identify all tables accessed during a particular period of
usage?
Used SQL Profiler and set up the following:
Objects: Object:Closed & Object:Opened.
TSQL SQL: BatchStarting & SQL:StmtCompleted.
Columns:
DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
Filter: Applied a filer to the databaseId as I wanted to identify only
tables accessed in a particular DB.
But on running a simple SQL SELECT – the following columns “DatabaseName,
ObjectId and ObjectName” columns were empty.
Why is this?
Please let me know what I’m missing out on?
(Note: A similar question had been posted earlier on – but there was no
satisfactory answer to it.)
Cheers!
SQLCatz
This is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running traces on SQL Server 7.0
and earlier. These objects do not exist in SQL Server 2000.
What events did you get in the trace? If you get TSQL SQL: BatchStarting & SQL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular tables using the TextData
column. this is so far the only reliable way I found to audit access for a set of tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:82798F82-2F21-44B8-B0D7-926DB4F8CBF5@.microsoft.com...
> How do I get to identify all tables accessed during a particular period of
> usage?
> Used SQL Profiler and set up the following:
> Objects: Object:Closed & Object:Opened.
> TSQL SQL: BatchStarting & SQL:StmtCompleted.
> Columns:
> DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
> Filter: Applied a filer to the databaseId as I wanted to identify only
> tables accessed in a particular DB.
> But on running a simple SQL SELECT – the following columns “DatabaseName,
> ObjectId and ObjectName” columns were empty.
> Why is this?
> Please let me know what I’m missing out on?
> (Note: A similar question had been posted earlier on – but there was no
> satisfactory answer to it.)
> Cheers!
> SQLCatz
|||Hello Tibor,
Thank you for the prompt reply!
I do not want to filter on certain tables - want to get all the tables that
the t-sql statements access while a set of scripts is running. In order to
filter the tables - I need to have all their names. In this case - I do not
have the complete list. Was hoping that by running the profiler - I'd be able
to get all the tables that were accessed.
Cheers!
SQLCatz

How do I get to identify all tables accessed?

How do I get to identify all tables accessed during a particular period of
usage?
Used SQL Profiler and set up the following:
Objects: Object:Closed & Object:Opened.
TSQL SQL: BatchStarting & SQL:StmtCompleted.
Columns:
DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
Filter: Applied a filer to the databaseId as I wanted to identify only
tables accessed in a particular DB.
But on running a simple SQL SELECT – the following columns “DatabaseName
,
ObjectId and ObjectName” columns were empty.
Why is this?
Please let me know what I’m missing out on?
(Note: A similar question had been posted earlier on – but there was no
satisfactory answer to it.)
Cheers!
SQLCatzThis is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running t
races on SQL Server 7.0
and earlier. These objects do not exist in SQL Server 2000.
What events did you get in the trace? If you get TSQL SQL: BatchStarting & S
QL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular ta
bles using the TextData
column. this is so far the only reliable way I found to audit access for a s
et of tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:82798F82-2F21-44B8-B0D7-926DB4F8CBF5@.microsoft.com...
> How do I get to identify all tables accessed during a particular period of
> usage?
> Used SQL Profiler and set up the following:
> Objects: Object:Closed & Object:Opened.
> TSQL SQL: BatchStarting & SQL:StmtCompleted.
> Columns:
> DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
> Filter: Applied a filer to the databaseId as I wanted to identify only
> tables accessed in a particular DB.
> But on running a simple SQL SELECT – the following columns “DatabaseNa
me,
> ObjectId and ObjectName” columns were empty.
> Why is this?
> Please let me know what I’m missing out on?
> (Note: A similar question had been posted earlier on – but there was no
> satisfactory answer to it.)
> Cheers!
> SQLCatz|||Hello Tibor,
Thank you for the prompt reply!
I do not want to filter on certain tables - want to get all the tables that
the t-sql statements access while a set of scripts is running. In order to
filter the tables - I need to have all their names. In this case - I do not
have the complete list. Was hoping that by running the profiler - I'd be abl
e
to get all the tables that were accessed.
Cheers!
SQLCatz

How do I get to identify all tables accessed?

How do I get to identify all tables accessed during a particular period of
usage?
Used SQL Profiler and set up the following:
Objects: Object:Closed & Object:Opened.
TSQL SQL: BatchStarting & SQL:StmtCompleted.
Columns:
DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
Filter: Applied a filer to the databaseId as I wanted to identify only
tables accessed in a particular DB.
But on running a simple SQL SELECT â' the following columns â'DatabaseName,
ObjectId and ObjectNameâ' columns were empty.
Why is this?
Please let me know what Iâ'm missing out on?
(Note: A similar question had been posted earlier on â' but there was no
satisfactory answer to it.)
Cheers!
SQLCatzThis is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running traces on SQL Server 7.0
and earlier. These objects do not exist in SQL Server 2000.
What events did you get in the trace? If you get TSQL SQL: BatchStarting & SQL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular tables using the TextData
column. this is so far the only reliable way I found to audit access for a set of tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:82798F82-2F21-44B8-B0D7-926DB4F8CBF5@.microsoft.com...
> How do I get to identify all tables accessed during a particular period of
> usage?
> Used SQL Profiler and set up the following:
> Objects: Object:Closed & Object:Opened.
> TSQL SQL: BatchStarting & SQL:StmtCompleted.
> Columns:
> DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
> Filter: Applied a filer to the databaseId as I wanted to identify only
> tables accessed in a particular DB.
> But on running a simple SQL SELECT â' the following columns â'DatabaseName,
> ObjectId and ObjectNameâ' columns were empty.
> Why is this?
> Please let me know what Iâ'm missing out on?
> (Note: A similar question had been posted earlier on â' but there was no
> satisfactory answer to it.)
> Cheers!
> SQLCatz|||Hello Tibor,
Thank you for the prompt reply!
I do not want to filter on certain tables - want to get all the tables that
the t-sql statements access while a set of scripts is running. In order to
filter the tables - I need to have all their names. In this case - I do not
have the complete list. Was hoping that by running the profiler - I'd be able
to get all the tables that were accessed.
Cheers!
SQLCatz

Sunday, February 19, 2012

How do I determine the table from index name

I ran a query to identify indexes with fragmentation problems, and it gives
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?What query/mechanism are you using to generate the list of fragmented
indexes?
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||First, you might be re-inventing the wheel. You will find code in Books Online which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
database id as second parameter (very useful).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query that
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:
> First, you might be re-inventing the wheel. You will find code in Books Online which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
> >I ran a query to identify indexes with fragmentation problems, and it gives
> > me the index names. I cannot tell from the names what tables or views are
> > being indexed. I have been wondering through the system views, but so far
> > nothing jumps out at me (i.e. sys.table_indexes).
> > I don't think that this is something that has to be solved, since I rebuild
> > indexes once a week, but I would like to know: Starting wtih
> > sys.indexes.object_id, how can I determine the table or view name?
>|||Probably a service broker queue. Perhaps you are using SB explicitly or for the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since that
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query that
> shows that it is actually the name of the table or view - which I just said
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
>> First, you might be re-inventing the wheel. You will find code in Books Online which does
>> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and
>> for
>> 2005, sys.dm_db_index_physical_stats.
>> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes
>> a
>> database id as second parameter (very useful).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>> >I ran a query to identify indexes with fragmentation problems, and it gives
>> > me the index names. I cannot tell from the names what tables or views are
>> > being indexed. I have been wondering through the system views, but so far
>> > nothing jumps out at me (i.e. sys.table_indexes).
>> > I don't think that this is something that has to be solved, since I rebuild
>> > indexes once a week, but I would like to know: Starting wtih
>> > sys.indexes.object_id, how can I determine the table or view name?|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>> My question began with a defragmentation query that I found in an Sql
>> 2005
>> textbook, which produced a list of six suspects with OBJECT_NAME
>> (dt.object_id) = queue_messages_1003150619 or something similar. Since
>> that
>> certainly didn't match any table or view in the database, I assumed it
>> was
>> the name of an index. But the response from Dan Guzman includes a query
>> that
>> shows that it is actually the name of the table or view - which I just
>> said
>> doesn't exist. So now I'm really confused.
>> "Tibor Karaszi" wrote:
>> First, you might be re-inventing the wheel. You will find code in Books
>> Online which does
>> defragmentation based on fragmentation level. For 2000, look in the DBCC
>> SHOWCONTIG topic, and for
>> 2005, sys.dm_db_index_physical_stats.
>> You can use the OBJECT_NAME function to resolve id to name. As of 2005
>> with sp2, this even takes a
>> database id as second parameter (very useful).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
>> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>> >I ran a query to identify indexes with fragmentation problems, and it
>> >gives
>> > me the index names. I cannot tell from the names what tables or views
>> > are
>> > being indexed. I have been wondering through the system views, but so
>> > far
>> > nothing jumps out at me (i.e. sys.table_indexes).
>> > I don't think that this is something that has to be solved, since I
>> > rebuild
>> > indexes once a week, but I would like to know: Starting wtih
>> > sys.indexes.object_id, how can I determine the table or view name?
>

Friday, March 23, 2012

How do I identify whether a column is set up to automatically increment

How do I identify the replication generated column(s) in a table

How do I identify dependencies before adding a column to a table?

How do I identify a command in a job step?

Wednesday, March 21, 2012

How do I get to identify all tables accessed?

How do I get to identify all tables accessed?

How do I get to identify all tables accessed?

Sunday, February 19, 2012

How do I determine the table from index name

How do I determine the table from index name

How do I determine the table from index name

display SQL Server

Blog Archive

About Me