Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to count rows for a data dictionary

Author  Topic 

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-23 : 23:43:08

The following code produces a data dictionary. I have been requested to add a "count" column to the out put that contains a count of the non Null rows for each column in the table. For example I know column notes has 60 non Null rows populated. So in the output in the count column I would see 60 on the Notes row.

I have no idea how to modify my code to accomplish this. Help Please!

Thank you


select * from
(SELECT 'lists' AS 'TABLE_NAME', *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'Lists', 'column', default))c
INNER JOIN
(SELECT * FROM INFORMATION_SCHEMA.columns)d
ON c.objname = d.column_name COLLATE Latin1_General_CI_AI
AND c.TABLE_NAME = d.TABLE_NAME
ORDER BY d.Ordinal_Position

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-24 : 03:55:30
Put the current results on a temp table created with an additional Identity column Row_No & nullable column NonNullCount for storing count

and write dynamic sql as
DECLARE @ID int,@Sql varchar(7000)

SET @ID=MIN(ID)--setting starting value of loop counter
FROM #Temp_Table


WHILE @ID IS NOT NULL--loop through table for getting count of all
--columns
BEGIN
SET @ColumnName=column_name FROM #Temp_Table WHERE Row_No=@ID--get each column names from temp table

SET @Sql='UPDATE #t SET NonNullCount=Count
FROM #Temp_Table #t
INNER JOIN (SELECT ''' +@ColumnName +''' AS ''Column_Name'',Count(*)AS ''Count'' FROM Lists WHERE ' + @ColumnName + ' IS NOT NULL)temp
ON #t.column_name=temp.Column_Name'
EXEC(@Sql)

SELECT @ID=MIN(Row_No) FROM #Temp_Table WHERE Row_No >@ID
END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-24 : 03:57:38
slight change. Hopefully this will work (i've not tested this):-

DECLARE @ID int,@Sql varchar(7000)

SET @ID=MIN(Row_No)--setting starting value of loop counter
FROM #Temp_Table


WHILE @ID IS NOT NULL--loop through table for getting count of all
--columns
BEGIN
SET @ColumnName=column_name FROM #Temp_Table WHERE Row_No=@ID--get each column names from temp table

SET @Sql='UPDATE #t SET NonNullCount=Count
FROM #Temp_Table #t
INNER JOIN (SELECT ''' +@ColumnName +''' AS ''Column_Name'',Count(*)AS ''Count'' FROM Lists WHERE ' + @ColumnName + ' IS NOT NULL)temp
ON #t.column_name=temp.Column_Name'
EXEC(@Sql)

SELECT @ID=MIN(Row_No) FROM #Temp_Table WHERE Row_No >@ID
END
Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-25 : 15:46:04
Looking at you r code gave me an idea as to how to approach the problem. But i am getting a strange error.

multi-part could not be bound error when trying to COUNT()

I have created the following temp table.

--DROP TABLE #temp_Table
--select 0 as Populated, c.Table_name, Column_name, Ordinal_position, Value, Data_type, Character_maximum_length
-- INTO #temp_Table
-- from
--(SELECT 'lists' AS 'TABLE_NAME', *
-- FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'Lists', 'column', default))c
--INNER JOIN
-- (SELECT * FROM INFORMATION_SCHEMA.columns)d
-- ON c.objname = d.column_name COLLATE Latin1_General_CI_AI
-- AND c.TABLE_NAME = d.TABLE_NAME
--ORDER BY d.Ordinal_Position
--select * from #Temp_Table

The temp table above #Temp_Table has these 2 columns among others.
Table_Name, Column_name
Lists.......ListID
Lists.......ItemName
Lists.......ItemAbbreviation
Lists.......ItemDescription

From the table named in the Table_Name column I want to populate a new column named "Populated" with the count of rows based on this select:
SELECT COUNT(*) FROM Lists WHERE Len(Notes)<> 0

So that I will end up with:
Table_Name ...Column_name ........Populated
Lists...............ListID......................22
Lists...............ItemName...............10
Lists...............ItemAbbreviation......20
Lists...............ItemDescription.......5

My attempt at writing this select is as follows.

select *, populated from #Temp_Table #t
--loop through table for getting count of all columns.
WHILE #t.Ordinal_position IS NOT NULL
begin
Update #t
set populated = (SELECT COUNT(*) FROM Lists WHERE Len(Notes)<> 0)
end

And get this error

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "#t.Ordinal_position" could not be bound.

How do i correct this?


Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-25 : 18:12:12
Found the poblem. Do not use WHILE with the UPDATE statement.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-25 : 23:28:52
Its not the problem of using while with update. Here you tried to reference table column directly on the While condition. Get the required table value on a variable and then use it for iteration inside while (like the one i have created in my example @ID) then assign incremental values inside the loop.
Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-26 : 01:12:23
This is the structure of #Temp_Table:

Populated...Table_name.....Column_name.....Ordinal_position
0........... lists............ListID..............1
0............lists............ListNameID..........2

I have been trying to get the syntax right that you suggested. But i can't get it. Here is what i have.

DECLARE @ID int,@Sql varchar(7000), @ColumnName varchar(50), @TableName varchar(50)
--setting starting value of loop counter
SET @ID=MIN(Ordnial_position)
select * FROM #Temp_Table
--loop through table for getting count of all
--columns
WHILE @ID IS NOT NULL
BEGIN
--get each column name from #Temp_Table
SET @ColumnName = #t.column_name FROM #Temp_Table #t WHERE Ordinal_position=@ID
SET @TableName = #t.Table_name FROM #Temp_Table #t WHERE Ordinal_position=@ID
SET @Sql='Update #temp_table
set populated FROM
INNER JOIN (SELECT COUNT(*) FROM ' + @TableName +
' WHERE Len(' + @ColumnName + ')<> 0) temp
WHERE Table_Name = @TableName
ON #t.column_name=temp.Column_Name'
end
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-26 : 03:13:17
the problem is with syntax of update statement within D SQL. Change like this and try if it works:-
DECLARE @ID int,@Sql varchar(7000), @ColumnName varchar(50), @TableName varchar(50)
--setting starting value of loop counter
SET @ID=MIN(Ordinal_position)
FROM #Temp_Table
--loop through table for getting count of all
--columns
WHILE @ID IS NOT NULL
BEGIN
--get each column name from #Temp_Table
SELECT @ColumnName = #t.column_name,@TableName = #t.Table_name FROM #Temp_Table #t WHERE Ordinal_position=@ID

SET @Sql='UPDATE #t
SET #t.populated = temp.Count FROM #temp_table #t
INNER JOIN (SELECT '''+ @ColumnName +''' AS ''column_name'',COUNT(*) AS ''Count'' FROM ' + @TableName +
' WHERE Len(''' + @ColumnName + ''')<> 0) temp
ON #t.column_name=temp.Column_Name
WHERE #t.Table_Name =' + @TableName +
' AND #t.Column_Name =' + @ColumnName

EXEC(@sql)

SET @ID=MIN(Ordinal_position)
FROM #Temp_Table
WHERE Ordinal_position > @ID
END
Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-26 : 10:20:53
When I run the SQL check on the suggested code I get the following errors

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'FROM'.

If i change the FROM statements by adding SELECT * the SQL check works with no error.

But when i run the code i get this error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Ordinal_position'.
Msg 207, Level 16, State 1, Line 22
Invalid column name 'Ordinal_position'.

This is the un modified code:

DECLARE @ID int,@Sql varchar(7000), @ColumnName varchar(50), @TableName varchar(50)
--setting starting value of loop counter
SET @ID=MIN(Ordinal_position)

FROM #Temp_Table
--loop through table for getting count of all columns
WHILE @ID IS NOT NULL
BEGIN

--get each column name from #Temp_Table
SELECT @ColumnName = #t.column_name,@TableName = #t.Table_name FROM #Temp_Table #t WHERE Ordinal_position=@ID

SET @Sql='UPDATE #t
SET #t.populated = temp.Count FROM #temp_table #t
INNER JOIN (SELECT '''+ @ColumnName +''' AS ''column_name'',COUNT(*) AS ''Count'' FROM ' + @TableName + ' WHERE Len(''' + @ColumnName + ''')<> 0) temp
ON #t.column_name=temp.Column_Name
WHERE #t.Table_Name =' + @TableName +
' AND #t.Column_Name =' + @ColumnName

EXEC(@sql)

SET @ID=MIN(Ordinal_position)
FROM #Temp_Table
WHERE Ordinal_position > @ID
END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-26 : 12:37:25
Try with SELECT:-
DECLARE @ID int,@Sql varchar(7000), @ColumnName varchar(50), @TableName varchar(50)

--setting starting value of loop counter
SELECT @ID=MIN(Ordinal_position)
FROM #Temp_Table
--loop through table for getting count of all columns
WHILE @ID IS NOT NULL
BEGIN

--get each column name from #Temp_Table
SELECT @ColumnName = #t.column_name,@TableName = #t.Table_name FROM #Temp_Table #t WHERE Ordinal_position=@ID

SET @Sql='UPDATE #t
SET #t.populated = temp.Count FROM #temp_table #t
INNER JOIN (SELECT '''+ @ColumnName +''' AS ''column_name'',COUNT(*) AS ''Count'' FROM ' + @TableName + ' WHERE Len(''' + @ColumnName + ''')<> 0) temp
ON #t.column_name=temp.Column_Name
WHERE #t.Table_Name =' + @TableName +
' AND #t.Column_Name =' + @ColumnName

EXEC(@sql)

SELECT @ID=MIN(Ordinal_position)
FROM #Temp_Table
WHERE Ordinal_position > @ID
END
Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-26 : 13:55:05
When I run the suggested code I get this error. Is thert something to do with the scope of the variables?:

Msg 207, Level 16, State 1, Line 5
Invalid column name 'lists'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ListID'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'lists'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ListNameID'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'lists'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ListOrder'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'lists'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ItemName'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'lists'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ItemAbbreviation'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'lists'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'ItemDescription'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'lists'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'Notes'.


#Temp_table contains
Table_Name.....column_name.....Ordinal_position
lists ListID 1
lists ListNameID 2
lists ListOrder 3
lists ItemName 4
lists ItemAbbreviation 5
lists ItemDescription 6
lists Notes 7

DECLARE @ID int,@Sql varchar(7000), @ColumnName varchar(50), @TableName varchar(50)

--setting starting value of loop counter
SELECT @ID=MIN(Ordinal_position)
FROM #Temp_Table
--loop through table for getting count of all columns
WHILE @ID IS NOT NULL
BEGIN

--get each column name from #Temp_Table
SELECT @ColumnName = #t.column_name,@TableName = #t.Table_name FROM #Temp_Table #t WHERE Ordinal_position=@ID

SET @Sql='UPDATE #t
SET #t.populated = temp.Count FROM #temp_table #t
INNER JOIN (SELECT '''+ @ColumnName +''' AS ''column_name'',COUNT(*) AS ''Count'' FROM ' + @TableName + ' WHERE Len(''' + @ColumnName + ''')<> 0) temp
ON #t.column_name=temp.Column_Name
WHERE #t.Table_Name =' + @TableName +
' AND #t.Column_Name =' + @ColumnName

EXEC(@sql)

SELECT @ID=MIN(Ordinal_position)
FROM #Temp_Table
WHERE Ordinal_position > @ID
END
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-27 : 03:27:45
I've written the code for handling the scenario you explained. I've created a temp table and populated with table name, column name,data type,ordinal position,... of all tables in a db. then i've written a loop to update the count field in temp table which gives count of each not null column values in table. I've tested this piece of code and it worked fine for me giving details of all columns of all tables in db along with thier datatype & count of non null values


CREATE TABLE #PropTable
(
Row_No int IDENTITY(1,1),
TABLE_NAME varchar(100),
COLUMN_NAME varchar(100),
DATA_TYPE varchar(50),
MS_Description varchar(7000),
Ordinal_Position int,
ValueCount bigint
)
--List all the tables in the database.
INSERT INTO #PropTable (TABLE_NAME,COLUMN_NAME,DATA_TYPE,Ordinal_Position)
SELECT tbls.TABLE_NAME,cln.COLUMN_NAME,cln.DATA_TYPE,cln.ORDINAL_POSITION FROM INFORMATION_SCHEMA.columns cln
Inner join INFORMATION_SCHEMA.TABLES tbls
on tbls.TABLE_NAME = cln.TABLE_NAME
Inner join (SELECT objname
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', default, NULL, NULL)) tbldescn
ON tbldescn.objname=tbls.TABLE_NAME


DECLARE @ID int,@TableName varchar(100),@ColumnName varchar(100),@sql varchar(8000)

SELECT @ID= MIN (Row_No)
FROM #PropTable

WHILE @ID IS NOT NULL
BEGIN
SELECT @TableName=TABLE_NAME,@ColumnName=COLUMN_NAME FROM #PropTable WHERE Row_No=@ID
SET @Sql='UPDATE #PropTable SET ValueCount =temp.Count FROM (SELECT COUNT(*) AS ''Count'' FROM ' + @TableName +' WHERE ' + @ColumnName + ' IS NOT NULL)temp WHERE TABLE_NAME= '''+@TableName+''' AND COLUMN_NAME= '''+ @ColumnName+''''-- AND CAST('+ @ColumnName +' AS varchar(8000))<>'''')temp'

EXEC (@Sql)

SET @Sql='UPDATE #PropTable SET MS_Description=CONVERT(varchar(7000),value) FROM ::fn_listextendedproperty (NULL, ''user'', ''dbo'', ''table'', '''+ @TableName+''', ''column'',''' +@ColumnName+''') WHERE TABLE_NAME= '''+@TableName+''' AND COLUMN_NAME= '''+ @ColumnName+''''


EXEC (@Sql)

SELECT @ID= MIN (Row_No)
FROM #PropTable
WHERE Row_No >@ID
END

---end of soln


SELECT * FROM #PropTable
Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-27 : 18:34:37
Thank you very much!!! Now I understand how this all works together. I Really appreciate all your help!
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-27 : 20:24:24
I read through this post.. I tried to optimize this code a little, Plus I do not see the need for ::FN_ListExtendedProperty.

Can you please clarify if you are sure you need that.

Below is code that will count the non null values in each column of each table. It should run a little faster then the other queries.



drop table #tbl1
drop table #tbl2
Create Table #Tbl2(RowID int, MyCount int)

SELECT *,Row_Number() over (order By Table_Name,Ordinal_Position) as RowID
Into #Tbl1
FROM INFORMATION_SCHEMA.columns a
Inner Join sysObjects b
on a.Table_Name = b.[Name]
where xtype = 'U'
and not Data_Type = 'Image'

Declare @ColumnName varchar(50),
@TableName varchar(50),
@RowID int,
@MaxID int
set @RowID = 1
Select @MaxID = Max(RowID) from #Tbl1

CREATE UNIQUE INDEX IX_Tbl1 ON #Tbl1 (RowID)

While @RowID <= @MaxID
Begin
select @ColumnName = Column_Name,@TableName = Table_Name,@RowID = RowID
From #Tbl1 where RowID = @RowID

Insert Into #Tbl2(RowID,MyCount)
exec('Select ' + @RowID + ', count(' + @ColumnName + ' ) from ' + @TableName )
set @RowID = @RowID + 1
End
CREATE UNIQUE INDEX IX_Tbl2 ON #Tbl2 (RowID)
Select * from #Tbl1 a Inner Join #tbl2 b on a.RowID = b.RowID

Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-28 : 14:54:30
- visakh16 -

I ran your suggested code and found two problems. One I was able to solve.

Running the code as suggested got this error:

Msg 468, Level 16, State 9, Line 12
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

I did some research and no one seems to know why the collation in a new install is some times different between the master and user databases. I just happens. The solution is to manually set the collation in the problem code.

To determine the collation of my main db I ran:
print 'My database [' + DB_NAME() + '] collation is: ' + cast( DATABASEPROPERTYEX ( db_name(), N'Collation' ) as varchar(128) )

I now know the collation of my main db is: SQL_Latin1_General_CP1_CI_AS


When I run:
SELECT table_name, column_name, collation_name FROM INFORMATION_SCHEMA.columns where table_name = 'Lists'

The collation seems to be correct.

Lists ListID NULL
Lists ListNameID NULL
Lists ListOrder SQL_Latin1_General_CP1_CI_AS
Lists ItemName SQL_Latin1_General_CP1_CI_AS
Lists ItemAbbreviation SQL_Latin1_General_CP1_CI_AS
Lists ItemDescription SQL_Latin1_General_CP1_CI_AS
Lists Notes SQL_Latin1_General_CP1_CI_AS

So I assumed fn_listextendedproperty must have the incorrect collation. So I forced INFORMATION_SCHEMA to match in the join.

ON tbldescn.objname=tbls.TABLE_NAME COLLATE Latin1_General_CI_AI

With that change the code executed with out a problem.

Which leads to the second problem which you may have an answer to. When I looked at the output I noticed that some of the tables were missing. When I ran
SELECT * FROM INFORMATION_SCHEMA.tables

I get:
........Provider
........County
........MSA
........ContactInfo
........IdentifierInfo
........ProviderFacility
........Address
........Facility
........FacilityAddress
........ProviderAddress
........Credentials
........FacilityRelationship
........FacilityAlias
........FacilityDepartment
........sysdiagrams

These are not all the tables in my DB. For example, note above when I ran
INFORMATION_SCHEMA.columns where table_name = 'Lists' I got the correct return values.

Any ideas why INFORMATION_SCHEMA.tables does not return all the tables in my DB?
Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-28 : 14:58:22
- Vinnie881 -

Thank you for the input.

I am attempting to build a basic data dictionary that has a count for each column.

* fn_listextendedproperty provides the extended property value(s) for the columns.
* INFORMATION_SCHEMA provides the column meta data.

So fn_listextendedproperty is required.

I ran your suggestion. I got this error.

(258 row(s) affected)
Msg 208, Level 16, State 0, Line 18
Invalid object name '#Tbl1'.

I have been trying to figure out the problem but without success. I am very new to SQL. What is causing the error?

Executed code...

drop table #tbl1
drop table #tbl2
Create Table #Tbl2(RowID int, MyCount int)

SELECT *,Row_Number() over (order By Table_Name,Ordinal_Position) as RowID
--Into #Tbl1
FROM INFORMATION_SCHEMA.columns a
Inner Join sysObjects b
on a.Table_Name = b.[Name]
where xtype = 'U'
and not Data_Type = 'Image'

Declare @ColumnName varchar(50),
@TableName varchar(50),
@RowID int,
@MaxID int
set @RowID = 1
Select @MaxID = Max(RowID) from #Tbl1

CREATE UNIQUE INDEX IX_Tbl1 ON #Tbl1 (RowID)

While @RowID <= @MaxID
Begin
select @ColumnName = Column_Name,@TableName = Table_Name,@RowID = RowID
From #Tbl1 where RowID = @RowID

Insert Into #Tbl2(RowID,MyCount)
exec('Select ' + @RowID + ', count(' + @ColumnName + ' ) from ' + @TableName )
set @RowID = @RowID + 1
End
CREATE UNIQUE INDEX IX_Tbl2 ON #Tbl2 (RowID)
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-28 : 20:57:59
You can re-paste the code, I fixed the #tbl1 Error.

Make sure to add your join to fn_listextendedproperty to the infoschema if you need that info.
Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-29 : 00:03:27
RE-past what code? Did I miss something?
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-29 : 00:53:48
quote:
Originally posted by kirknew2SQL
I ran your suggestion. I got this error.

(258 row(s) affected)
Msg 208, Level 16, State 0, Line 18
Invalid object name '#Tbl1'.

I have been trying to figure out the problem but without success. I am very new to SQL. What is causing the error?




I edited that code in my post so if you re-copy it, it should run without the error.
Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-29 : 01:39:13
When I run the code the result grid flashes and the following error appears:

(258 row(s) affected)
Msg 208, Level 16, State 0, Line 16
Invalid object name '#Tbl1'.

I don't see the count column in the result set. I am thinking that the above error os preventing the exec() from happening. Though I am still at a loss as to the cause of the error.

executed code...

drop table #tbl1
drop table #tbl2
Create Table #Tbl2(RowID int, MyCount int)

SELECT *,Row_Number() over (order By Table_Name,Ordinal_Position) as RowID
--Into #Tbl1
FROM INFORMATION_SCHEMA.columns a
Inner Join sysObjects b
on a.Table_Name = b.[Name]
where xtype = 'U'
and not Data_Type = 'Image'

Declare @ColumnName varchar(50),
@TableName varchar(50),
@RowID int,
@MaxID int
set @RowID = 1
Select @MaxID = Max(RowID) from #Tbl1

CREATE UNIQUE INDEX IX_Tbl1 ON #Tbl1 (RowID)

While @RowID <= @MaxID
Begin
select @ColumnName = Column_Name,@TableName = Table_Name,@RowID = RowID
From #Tbl1 where RowID = @RowID

Insert Into #Tbl2(RowID,MyCount)
exec('Select ' + @RowID + ', count(' + @ColumnName + ' ) from ' + @TableName )
set @RowID = @RowID + 1
End
CREATE UNIQUE INDEX IX_Tbl2 ON #Tbl2 (RowID)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-29 : 03:07:33
Hi kirknew2SQL
the probelm was with the last join in temp table population statement . just change that to left outer join and you will get all tables info ( There may be tables without the extend properties being set)

SELECT tbls.TABLE_NAME,cln.COLUMN_NAME,cln.DATA_TYPE,cln.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.columns cln
Inner join INFORMATION_SCHEMA.TABLES tbls
on tbls.TABLE_NAME = cln.TABLE_NAME
Left outer join (SELECT objname
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', default, NULL, NULL)) tbldescn
--ON tbldescn.objname=tbls.TABLE_NAME
ON tbldescn.objname=tbls.TABLE_NAME COLLATE Latin1_General_CI_AI
Go to Top of Page
    Next Page

- Advertisement -