November 19, 2007 at 4:07 am
Can anyone please give me the equivalent sql for sql server 2000 for the following two queries
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename',
case y.name
when 'varchar' then convert(varchar, c.max_length)
when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)
else ''
end attrib,y.*
from sys.tables t, sys.columns c, sys.types y
where t.object_id = c.object_id
and t.name not in ('sysdiagrams')
and c.system_type_id = y.system_type_id
and c.system_type_id = y.user_type_id
order by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_key
from sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns c
where t.object_id = i.object_id
and t.object_id = ic.object_id
and t.object_id = c.object_id
and i.index_id = ic.index_id
and c.column_id = ic.column_id
and t.name not in ('sysdiagrams')
order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server data base[2005 version i also need this for sql server 2000 version]
November 19, 2007 at 10:36 pm
Salam,
--1 -- Table info
create PROCEDURE Generate_getDataDictionary
AS
DECLARE @table_name nvarchar(128)
CREATE table #tblDataDictionary
(table_name [sql_variant],
column_order [sql_variant],
column_name [sql_variant],
column_datatype [sql_variant],
column_length [sql_variant],
column_precision [sql_variant],
column_scale [sql_variant],
column_allownull [sql_variant],
column_default [sql_variant],
column_description [sql_variant])
DECLARE tablenames_cursor CURSOR FOR
SELECT name FROM sysobjects where type = 'U' and status > 1 order by name
OPEN tablenames_cursor
FETCH NEXT FROM tablenames_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
--CODE FOR THE COLUMNS
INSERT INTO #tblDataDictionary
SELECT
obj.[name] AS 'table_name',
col.colorder AS 'column_order',
col.[name] AS 'column_name',
typ.[name] AS 'column_datatype',
col.[length] AS 'column_length',
CASE WHEN typ.[name] = 'decimal' THEN CAST(col.[prec] AS nvarchar(255)) ELSE '' END AS 'column_precision',
CASE WHEN typ.[name] = 'decimal' THEN CAST(col.[scale] AS nvarchar(255)) ELSE '' END AS 'column_scale',
convert(varchar(254), rtrim(substring(' YES',(ColumnProperty (col.id, col.name,'AllowsNull')*3)+1,3))),
ISNULL(com.text,'') AS 'column_default',
ISNULL(ext.value,'') AS 'column_description'
FROM sysobjects obj
INNER join syscolumns col on obj.id = col.id
INNER JOIN systypes typ ON col.xtype = typ.xtype
LEFT join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description', N'user',N'dbo',N'table', @table_name, N'column', null) ext on col.name = ext.objname
LEFT OUTER JOIN syscomments com ON col.cdefault = com.id
WHERE obj.name = @table_name
AND typ.[name] <> 'sysname'
ORDER BY col.colorder
--CODE ENDS HERE
FETCH NEXT FROM tablenames_cursor INTO @table_name
END
CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor
SELECT * FROM #tblDataDictionary ORDER BY table_name,Column_Order
GO
exec Generate_getDataDictionary
--2
--Get all indexes infos (index, primary key,..).
-------------------------------------------------
Set nocount on
Declare @objectid varchar (75)
--Create a temporary table to save indexes info.
Create table #tblindextemp
(
TableName Varchar (75) null,
index_name varchar (200),
index_description varchar(500),
index_cols varchar(200)
)
Declare getindexkeys cursor local static for
Select name
From m sysobjects where xtype='U' and name in (
Select object_name(id)
from sysindexes
Where object_name (id) in (Select name from sysobjects where
type in ('U','V') ) and indid=1 )
Open getindexkeys
Fetch next from getindexkeys into @objectid
While @@fetch_status=0
Begin
Insert into #tblindextemp (index_name,index_description,index_cols)
Execute sp_helpindex @objectid
Update #tblindextemp
set TableName =@objectid
where TableName is null
Fetch next from getindexkeys into @objectid
End
Close getindexkeys
Deallocate getindexkeys
Select * from #tblindextemp
Drop table #tblindextemp
Set nocount off
Regards,
Ahmed
November 19, 2007 at 11:20 pm
Walaikum-Salaam
Thanks Ahmed
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply