Monday, March 26, 2012
Query which system table to answer this
It simply puts a 0 into particular fields upon new record creation.
Is there a query I can run against a particular system table to give me a list of fields this default is applied against in the DB?
ThanksSqlSpec will give you this information, but I can't remember now what table it's hitting to get it. it may be as simple as querying sysdepends.
I'll take a look at the code tonight and post again.|||If I understand your query correctly (It's late and I'm tired!!) you can run exec sp_mshelpcolumns 'enter table name' and the col_dridefname column is the contraint name and the first text column is the default value.|||this give you all default usage by columns:
select
s.name as colname
,o1.name as tablename
,o2.name as defaultname
from
syscolumns s
inner join
sysobjects o1 on o1.id=s.id
inner join
sysobjects o2 on o2.id=s.cdefault
and this will give you default usage by udts:
exec sp_mshelptype @.typename=null, @.flags='uddt'
Wednesday, March 21, 2012
Query total number of records in a schema
Please help out the neophyte!
I'm trying to query a database for the total number of records in each table owned by a particular schema in SQL Server 2000. I can query INFORMATION_SCHEMA.TABLES for a list of the tables in a particular schema, but how do I pass these table names to variable that can be used to count(*) the records in each? Can anyone show me how this should be done using simple TSQL?
Thanks.
Chris
Give this a shot:
Code Snippet
DECLARE @.TableName varchar(100)
DECLARE @.SQL nvarchar(MAX)
DECLARE SchemaCounter CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
OPEN SchemaCounter
FETCH NEXT FROM SchemaCounter INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL = 'SELECT COUNT(*) [' + @.TableName + ' COUNT] FROM [' + @.TableName + ']'
EXEC sp_executesql @.SQL
FETCH NEXT FROM SchemaCounter INTO @.TableName
END
CLOSE SchemaCounter
DEALLOCATE SchemaCounter
|||There is a couple of ways to get it through T-SQL, but I would not call them "simple" in neither case:
Code Snippet
select o.name, i.rowcnt
from sysindexes i
inner join sysobjects o on o.id = i.id
where o.type = 'U'
and i.indid in (0,1)
Or this one (still works in MSSQL2005):
Code Snippet
exec sp_MStablespace 'MyTableName'
The only drawback of these methods is that they used undocumented or deprecated features of SQL Server. Only hope you know what the word "undocumented" really means in MSSQL.
PS There is also a way to use sp_MSForEachTable system stored proc, but it's also undocumented
Hey...
rowcnt in sysindexes
It is very dangerous to use. One can not assure it gives exact number of rows and I am not sure if we can use this or not?
Regards,
Subbu
|||cannot trust rowcnt in sysindexes. what do u say ?
|||Yes.. Here the stright forward query (without undocumented sps)
Code Snippet
Select
OBJ.NAME [TableName],
IND.RowCnt as [RowCount]
From
dbo.sysindexes IND
Join sysobjects OBJ on IND.ID = OBJ.ID and indid < 2 And OBJ.Type='U'
order By 1
|||Hey, the neophyte thanks you for your help. A nice simple solution to my problem.
Chris
|||RowCnt is not updated in "real time". It not ever guaranteed to be 100% accurate. However, if you only want a very reliable "ballpark" number, it works really well. I use it all the time with the caviat that the number is a guidline, rather than "in-stone."Monday, March 12, 2012
query to get list of tables from database
Thanksselect table_name from information_schema.tables where TABLE_TYPE = 'BASE TABLE'